current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
"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 17 hours ago

2 Answers 2

up vote 0 down vote accepted

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$$;
share|improve this answer
    
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 yesterday
    
@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." –  Threadid yesterday
    
EXECUTE is not necessary at all. This is a misunderstanding. –  Erwin Brandstetter 21 hours ago
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.