Returning from a function with OUT parameter

Question

I have an error, but I don't know what the problem is.

I want execute a function and return a value from a column filled in by the column default, a sequence - the equivalent of currval(sequence).

I use:
PostgreSQL 9.0
pgAdmin III

CREATE OR REPLACE FUNCTION name_function(in param_1 character varying
                                      , out param_2 bigint)
  AS
$$
BEGIN
    INSERT INTO table (collumn_seq,param_1) VALUES (DEFAULT,param_1)
    returning collumn_seq;
--where:collumn_seq reference a collumn serial..
END;
$$
  LANGUAGE plpgsql VOLATILE;

I can create the function without error but when trying to execute, the following error is returned:

SELECT name_function('GHGHGH');

ERROR: The query has no destination for result data

BTW, is this old question answered properly?

Erwin Brandstetter
– Erwin Brandstetter

2021-05-03 14:41:27 +00:00
Commented May 3, 2021 at 14:41 — Erwin Brandstetter
– Erwin Brandstetter, Commented May 3, 2021 at 14:41

Erwin Brandstetter · Accepted Answer · 2020-10-12 13:21:23Z

It would work like this:

CREATE OR REPLACE FUNCTION name_function(param_1 varchar
                                   , OUT param_2 bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
    INSERT INTO table (collumn_seq, param_1)  -- "param_1" also the column name?
    VALUES (DEFAULT, param_1)
    RETURNING collumn_seq
    INTO param2;
END
$func$;

Normally, you would add a RETURN statement, but with OUT parameters, this is optional.
Refer to the manual for more details:

The simple case can be covered with a plain SQL function.
And you can omit the target column that shall get its DEFAULT value.
And you can just as well use a RETURNS clause in this case:

CREATE OR REPLACE FUNCTION name_function(param_1 varchar)
  RETURNS bigint
  LANGUAGE sql AS
$func$
INSERT INTO table (param_1)  -- "param_1" also the column name?
VALUES (param_1)
RETURNING collumn_seq;
$func$;

Collectives™ on Stack Overflow

Returning from a function with OUT parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related