Using variables in SQL script

Question

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

• I am using an SQL script, not PL/pgSQL
• In MySQL I can do this: SELECT @id:=itemid FROM myTable;, then later on, I can use @id anywhere I like.

• Danilo Tommasina: If you are using Java: see stackoverflow.com/questions/241003/… Ensure that you are using a JDBC 4 driver.
• Bart Friederichs: The problem isn't getting the last ID (there is only one, so no problem there). The problem is how to SELECT a value and use it later in the script.
• wildplasser: Store it into a results table and retrieve it when you need it?
• Bart Friederichs: @wildplasser how do I retrieve it, to use them in the SELECT my_function() call?
• wildplasser: @BartFriederichs : see my answer

Answer 1

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)

• Bart Friederichs: Of course, as simple as SELECT myFun(id) FROM table. Should have thought of that myself.

Answer 2

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);

• Bart Friederichs: I am using a simple SQL script, no libraries, just something along the line of psql < test.sql.
• Stev_k: OK, have clarified the answer
• Bart Friederichs: Okay, and how can I use that did in my function call? In a plain SQL script? MySQL for example has @ for variables.
• Bart Friederichs: I found a solution using a DO block, which works fine as well (seems to be some kind of anonymous function)

Answer 3

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$$;