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.

If you are using Java: see http://stackoverflow.com/questions/241003/how-to-get-a-value-from-the-last-inserted-row Ensure that you are using a JDBC 4 driver. — Danilo Tommasina, Sep 30 '15 at 15:28
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. — Bart Friederichs, Sep 30 '15 at 15:34
Store it into a results table and retrieve it when you need it? — wildplasser, Sep 30 '15 at 15:39
@wildplasser how do I retrieve it, to use them in the `SELECT my_function()` call? — Bart Friederichs, Sep 30 '15 at 15:40

Stev_k · Answer 1 · 2015-09-30T15:48:26.753

1

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

edited Sep 30 '15 at 15:48

answered Sep 30 '15 at 15:26

Stev_k

2,118
3
22
36

I am using a simple SQL script, no libraries, just something along the line of `psql < test.sql`. – Bart Friederichs Sep 30 '15 at 15:35
OK, have clarified the answer – Stev_k Sep 30 '15 at 15:43
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 Sep 30 '15 at 15:44
I found a solution using a `DO` block, which works fine as well (seems to be some kind of anonymous function) – Bart Friederichs Sep 30 '15 at 15:52

wildplasser · Accepted Answer · 2015-09-30T16:20:12.967

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)

edited Sep 30 '15 at 16:20

answered Sep 30 '15 at 15:57

wildplasser

43,142
8
66
109

Of course, as simple as `SELECT myFun(id) FROM table`. Should have thought of that myself. – Bart Friederichs Sep 30 '15 at 16:00

score 0 · Answer 3 · edited Apr 13 '17 at 12:42

0

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Sep 30 '15 at 15:54

Bart Friederichs

33,050
15
95
195

Using variables in SQL script

3 Answers3

Linked