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.

Solution 1

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

Solution 2

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.

Comments

[Frank Conry]

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?

[Threadid]

@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: 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."

[Erwin Brandstetter]

EXECUTE is not necessary at all. This is a misunderstanding.