plpgsql: calling a function with 2 OUT parameters

Question

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.

Just use "select * from get_test_read();" to get named columns. — rschmidt13
– rschmidt13, Commented Mar 22, 2023 at 17:14

vyegorov · Accepted Answer · 2012-05-14 13:37:02Z

43

+500

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;

answered May 14, 2012 at 13:37

vyegorov

23k7 gold badges61 silver badges76 bronze badges

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

2 Comments

Muhammad Waheed Over a year ago

it gives me error as follows: ERROR: syntax error at or near "into"

user9645 Over a year ago

@Muhammed - You can only use SELECT... INTO... inside a function (with such named variables defined). the answer does not clearly indicate that. If you are doing this outside a function just omit the INTO... part.

Ejrr1085 · Accepted Answer · 2020-10-18 01:02:09Z

4

SELECT * INTO xx, yy FROM get_test();

UPDATE:

Explanation:

Modifying the second function:

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

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

END;
$$  LANGUAGE plpgsql;

This is similar to SELECT INTO with TABLE, where only get 2 values:

SELECT "FIELD1", "FIELD2" INTO variable1, variable2 FROM "TABLE" WHERE ...

Npgsql Basic Usage

PL/pgSQL Function Parameter Modes: IN, OUT, INOUT

edited Oct 18, 2020 at 1:02

answered Oct 17, 2020 at 20:46

Ejrr1085

1,0792 gold badges19 silver badges32 bronze badges

2 Comments

xShirase Over a year ago

Hey, thanks for the answer! Could you give a bit of explanation? It would help OP and future readers :)

Ejrr1085 Over a year ago

OK xShirase, I have modified my answer with an explanation

Collectives™ on Stack Overflow

plpgsql: calling a function with 2 OUT parameters

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related