Select integer returning function result into variable postgres

Question

In Postgres, I'm writing a test script for a function (stored procedure) I wrote. The function returns and integer, the id of the row inserted. In the test script I want to select that id into a variable and show everything has been inserted correctly.

The function looks like this:

CREATE FUNCTION create_factor(p_name VARCHAR(255))
  RETURNS integer AS
$$
DECLARE v_insert_id INTEGER;
BEGIN
    ....
    RETURN v_insert_id AS id;
END;
$$ LANGUAGE plpgsql;

The psql script looks like this:

BEGIN;

\i create_factor.sql

DO $$
declare factorId integer;

select create_factor into factorId from    /* have tried selecting * as well */
create_factor(
    'my factor'::VARCHAR(255)
);

\x

select * from factors where name='my factor' and id=factorId;

\x

select k.key_name, v.value
from factors f
join factor_type_key_store k on k.factor_type_id = f.factor_type_id
join factor_key_value_store v on v.factor_type_key_store_id=k.id ;

END$$;

ROLLBACK;

The error I get is as follows:

psql:create_factor_test.sql:31: ERROR: invalid type name "create_factor.id into factorId from

clearly referring to the select into statement. I've switched up both the function definition and the script quite a bit. Not sure what I'm missing.

*"show everything has been inserted correctly"* indicates your function `create_factor()` actually inserts a row. You should have said so explicitly. Also, you may want to start another question asking how to optimize that. It looks like what you have is not the optimum. And remember to mention your version of Postgres. — Erwin Brandstetter, Dec 27 '14 at 22:07

score 3 · Answer 1 · answered Dec 27 '14 at 18:44

Variable names and column aliases are not visible outside a function. Names can be set with OUT parameters. For a simple function returning a scalar value like yours the default column name defaults to the name of the function.

CREATE FUNCTION create_factor(p_name text)
  RETURNS integer AS
$func$
DECLARE
   v_insert_id INTEGER;
BEGIN
    ....
    RETURN v_insert_id;  -- AS id -- pointless noise
END;
$$ LANGUAGE plpgsql;

If you want to use a DO statement for a test (I don't see the point, but following your lead):

DO
$do$
DECLARE
   factor_id integer := create_factor('my factor');
BEGIN
   RAISE NOTICE 'New factor row: %'
       , (SELECT f FROM factors f WHERE name = 'my factor' AND id = factor_id);
END
$do$;

I assume you are aware that code inside a DO statement is PL/pgSQL by default, not plain SQL?

You can declare a variable in plpgsql code and assign to it on the same line.

But you cannot RETURN anything from a DO statement. You'd need another function for that. Raising a NOTICE probably does the job for you? Not knowing what you want to see, I return the whole row in the example.

Threadid · Accepted Answer · 2014-12-27T12:30:49.493

2

You need to use an EXECUTE .... INTO to assign your variable like this:

See documentation here

Function returns 1:

CREATE FUNCTION create_factor(   p_name VARCHAR(255)
                                    )
RETURNS integer AS $$
DECLARE v_insert_id INTEGER;
BEGIN
     v_insert_id:=1;
     RETURN v_insert_id AS id;
END;
$$ LANGUAGE plpgsql;

EXECUTE INTO assign the function result to your variable:

DO $$
declare factorId integer;
BEGIN
EXECUTE 'select * from 
create_factor(
    ''my factor''::VARCHAR(255)
);' INTO factorId;     /* have tried selecting * as well */

RAISE NOTICE 'factorID = %', factorId;

EXECUTE 'WITH f AS (select * from factors where name=''my factor'' and id=' || factorId || ')
select k.key_name, v.value from f join factor_type_key_store k on k.factor_type_id = f.factor_type_id join factor_key_value_store v on v.factor_type_key_store_id=k.id;';

END$$;

edited Dec 27 '14 at 12:30

answered Dec 27 '14 at 02:36

Threadid

730
1
7
27

This worked, but then I couldn't get the `selects` to work. putting them into `perform` statements or executing the as test strings works, but the output does not render in the psql console. Am I correct that this is a limitation of posgres/psql? – Frank Conry Dec 27 '14 at 04:42
@FrankConry The DO statement only returns void. DO is a postgresql only implementation. It has no compatibility to SQL. To make this repeatable and define the returned output you will need to create a stored procedure. See doc: http://www.postgresql.org/docs/9.3/static/sql-do.html - "The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time." – Threadid Dec 27 '14 at 12:42
`EXECUTE` is not necessary at all. This is a misunderstanding. – Erwin Brandstetter Dec 27 '14 at 18:03

Select integer returning function result into variable postgres

2 Answers2

Linked