1

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.
Improve this question
5
  • If you are using Java: see stackoverflow.com/questions/241003/… Ensure that you are using a JDBC 4 driver. Commented Sep 30, 2015 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. Commented Sep 30, 2015 at 15:34
  • Store it into a results table and retrieve it when you need it? Commented Sep 30, 2015 at 15:39
  • @wildplasser how do I retrieve it, to use them in the SELECT my_function() call? Commented Sep 30, 2015 at 15:40
  • @BartFriederichs : see my answer Commented Sep 30, 2015 at 15:58

3 Answers 3

Reset to default
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)

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

1 Comment

Of course, as simple as SELECT myFun(id) FROM table. Should have thought of that myself.
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);
Improve this answer

4 Comments

I am using a simple SQL script, no libraries, just something along the line of psql < test.sql.
OK, have clarified the answer
Okay, and how can I use that did in my function call? In a plain SQL script? MySQL for example has @ for variables.
I found a solution using a DO block, which works fine as well (seems to be some kind of anonymous function)
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$$;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.