CODEWITHSUNDEEP
postgresql
Bart Friederichs
Bart Friederichs

Reputation: 33511

Using variables in SQL script

I am writing a unit test for my PostgreSQL functionality. This starts with inserting data in the database and then calling a certain function. However, because I use auto-incrementing IDs, I cannot know what parameter to put in my function call.

I want to be able to do something like this:

INSERT INTO myTable ...;

SELECT id FROM myTable INTO l_id;

SELECT my_function(l_id);

Updates

Upvotes: 1

Views: 227

Answers (3)

wildplasser
wildplasser

Reputation: 44240

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE mytab
    ( id INTEGER NOT NULL PRIMARY KEY
    );
INSERT INTO mytab (id) SELECT gs FROM generate_series(1,10) gs;

CREATE OR REPLACE FUNCTION tmp.myfunc ( _int  integer )
RETURNS text AS
$func$

DECLARE ret text;

BEGIN
    ret = 'OMG_' || _int::text;
RETURN ret;
END;
$func$
LANGUAGE 'plpgsql'
    ;

SELECT myfunc(1);

SELECT myfunc(mt.id)
FROM mytab mt
    ;

Also, for smaller things you could use psql's \gset command : (link to documentation)

Upvotes: 1

Bart Friederichs
Bart Friederichs

Reputation: 33511

Here I found mentioning of the DO block, which seems to work as a anonymous function. I fixed it like this:

INSERT ...

DO $$
DECLARE
    l_id integer;
BEGIN
    SELECT id FROM table INTO l_id;
    SELECT myFunction(l_id);
END$$;

Upvotes: 0

Stev_k
Stev_k

Reputation: 2126

INSERT in Postgres returns an OID, which you can get in a number of ways

  • Using the returning clause in normal SQL, e.g.

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;

  • using the GET DIAGNOSTICS api in PL-PGSQL

  • using the return function in a helper library (syntax depending on library).

In your case, you could do something like

with res as (
INSERT INTO my_table (id, ...) VALUES(x,..)
RETURNING id)
SELECT my_function(res.id);

Upvotes: 1

Related Questions