Returning from a function with OUT parameter

by

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

More Related Contents:

  1. What are ‘$$’ used for in PL/pgSQL
  2. INSERT with dynamic table name in trigger function
  3. The forgotten assignment operator “=” and the commonplace “:=”
  4. Truncating all tables in a Postgres database
  5. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  6. How to set value of composite variable field using dynamic SQL
  7. How do I do large non-blocking updates in PostgreSQL?
  8. Execute a dynamic crosstab query
  9. Is there a way to disable function overloading in Postgres
  10. Can I make a plpgsql function return an integer without using a variable?
  11. PostgreSQL function returning multiple result sets
  12. Dynamic SQL (EXECUTE) as condition for IF statement
  13. SELECT raises exception in PL/pgSQL function
  14. Test for null in function with varying parameters
  15. Return SETOF rows from PostgreSQL function
  16. Error when setting n_distinct using a plpgsql variable
  17. Postgres FOR LOOP
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. Function to loop through and select data from multiple tables
  20. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  21. SQL Sub queries in check constraint
  22. Cursor based records in PostgreSQL
  23. ERROR: unterminated quoted string at or near
  24. How can I send some http request from postgresql function or trigger
  25. Select from a table variable
  26. Hashing a String to a Numeric Value in PostgreSQL
  27. Loop on tables with PL/pgSQL in Postgres 9.0+
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment