Trying to save and execute psql function script from command line

Question

I've created a function and saved it in a .sql file. I want to use this file and function to place the file on a system and then execute the included function via the command line, so the new data gets saved to the database on that system.

Currently, the content of my .sql file is:

CREATE OR REPLACE
FUNCTION device_authorisation_change(device_identification VARCHAR)
 RETURNS VARCHAR AS
$$
DECLARE 
  OWNER varchar(255);
  DEVICE_ID integer;
  OWNER_ID integer;
  DATE_TIME timestamp;

BEGIN
  OWNER := 'MyDefaultOrganisation';
  DATE_TIME := current_timestamp;

  DEVICE_ID := (SELECT id from device dev where dev.device_identification = $1);
  OWNER_ID := (SELECT id from owners own where own.owner_identification = OWNER);

  DELETE FROM device_authorization
  WHERE device = DEVICE_ID AND owner = OWNER_ID;

  INSERT INTO device_authorization(id, creation_time, device, owner)
  VALUES (nextval('device_authorization_id_seq'), DATE_TIME, DEVICE_ID, OWNER_ID);

  RAISE NOTICE 'Deleted device authorisations for % for device: %' , OWNER,  $1;

  RETURN '';
END;
$$ LANGUAGE plpgsql;

When I try to run the file using the command, I used the following command

psql -d my_database -f ChangeOwnership.sql -v device_identification='TestDevice'

and the result shows that de device_identification seems to be NULL. However, the function gets stored to the database and I can execute this function (in pgAdmin) using

SELECT "device_authorisation_change"('TestDevice');

This time, the function executes well.

What I want to achieve is that I can run the sql script from command line, using (named) arguments, which immediately updates the data in the database. It is not necessary to save the function in the database, I actually like to avoid it, but if it complicates things then it's not a problem if it stays in the database.

Can anyone help me out?

Read about DO statement which executes anonymous code block if you don't need to create a function :) — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 27, 2016 at 17:07

MatheusOl · Accepted Answer · 2016-08-27 17:00:34Z

1

If you want to avoid creating the function inside the database, it is actually easier to do it using psql's variables instead of a PL/pgSQL code:

\set owner 'MyDefaultOrganisation'
\set ON_ERROR_STOP 1
-- Let's open a transaction, so we do all or nothing
BEGIN;
  -- Execute the query and set variables with the output using \gset
  SELECT id AS device_id
  FROM device dev
  WHERE dev.device_identification = :'device_identification' \gset

  SELECT id AS owner_id
  FROM owners own
  WHERE own.owner_identification = :'owner' \gset

  DELETE FROM device_authorization
  WHERE device = :'device_id' AND owner = :'owner_id';

  INSERT INTO device_authorization(id, creation_time, device, owner)
  VALUES (nextval('device_authorization_id_seq'), current_timestamp, :'device_id', :'owner_id');
COMMIT;

Then you can save it and run exactly as you tried before:

psql -d my_database -f ChangeOwnership.sql -v device_identification='TestDevice'

You could even rewrite it all as a single statement using CTEs (Common Table Expression):

WITH dev_own AS (
    SELECT dev.id AS device_id, own.id AS owner_id
    FROM device dev, owner own
    WHERE
        dev.device_identification = :'device_identification'
        AND own.owner_identification = :'owner'
), insert_new AS (
    INSERT INTO device_authorization(id, creation_time, device, owner)
    SELECT nextval('device_authorization_id_seq'), current_timestamp, device_id, owner_id
    FROM dev_own;
)
DELETE FROM device_authorization
WHERE (device, owner) IN (SELECT device_id, owner_id FROM dev_own);

answered Aug 27, 2016 at 17:00

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joetjah Over a year ago

I could not run the file since the parameter \gset is unknown, but without it, everything works fine nevertheless! Thank you for the help!

MatheusOl Over a year ago

@Joetjah \gset is somewhat new, since PostgreSQL 9.3. The second version should work since 9.1

Laurenz Albe · Accepted Answer · 2016-08-27 04:33:57Z

1

Your function doesn't get called because you did not include the SELECT statement in the SQL script. There is only one statement there (CREATE FUNCTION), and that is duly executed. If you want to get rid of the function afterwards, you'd also need a DROP FUNCTION statement in the SQL script.

But since your function does not return anything meaningful, I'd suggest that you use a DO statement instead. That way you can execute PL/pgSQL code without having to define a function.

answered Aug 27, 2016 at 4:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Trying to save and execute psql function script from command line

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related