plpgsql: calling a function with 2 OUT parameters

postgresql plpgsql
marco picture marco · May 14, 2012 · Viewed 24.2k times · Source

I'm trying to fetch to values from a plpgsql function with 2 OUT paramenters but I have some problem.

These are the functions:

CREATE OR REPLACE FUNCTION get_test(OUT x text, OUT y text)
AS $$
BEGIN
   x := 1;
   y := 2;
END;
$$  LANGUAGE plpgsql;
----------------------------------------------------------------

CREATE OR REPLACE FUNCTION get_test_read()
RETURNS VOID AS $$
DECLARE
   xx text;
   yy text;
BEGIN

   SELECT get_test() INTO xx, yy;
   
   RAISE INFO 'x: <%>', xx;
   RAISE INFO 'y: <%>', yy;

END;
$$  LANGUAGE plpgsql;

The output of the command:

select get_test_read();

INFO: x: <(1,2)

INFO: y: <>

get_test_read

So both the values go to the first parameter. I cannot find some example on how to call a function like this.

Answer

vyegorov picture vyegorov · May 14, 2012

As you have 2 OUT params, your function will return a record.

In order to get all values you should use function as the source of your data and put it into the FROM clause like this:

SELECT * FROM get_test() INTO xx, yy;

Related questions

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More
PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More