Selecting and passing a record as a function argument

Question

It may look like a duplicate of existing questions (e.g. This one) but they only deal with passing "new" arguments, not selecting rows from the database.

I have a table, for example:

CREATE TABLE my_table (
    id bigserial NOT NULL,
    name text,
    CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

And a function:

CREATE FUNCTION do_something(row_in my_table) RETURNS void AS
$$
BEGIN
    -- does something
END;
$$
LANGUAGE plpgsql;

I would like to run it on data already existing in the database. It's no problem if I would like to use it from another PL/pgSQL stored procedure, for example:

-- ...
SELECT * INTO row_var FROM my_table WHERE id = 123; -- row_var is of type my_table%rowtype
PERFORM do_something(row_var);
-- ...

However, I have no idea how to do it using an "ordinary" query, e.g.

SELECT do_something(SELECT * FROM my_table WHERE id = 123);

ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT FROM do_something(SELECT * FROM my_table ...

Is there a way to execute such query?

score 3 · Accepted Answer · edited Jan 09 '16 at 05:12

You need to pass a scalar record to that function, this requires to enclose the actual select in another pair of parentheses:

SELECT do_something( (SELECT * FROM my_table WHERE id = 123) );

However the above will NOT work, because the function only expects a single column (a record of type my_table) whereas select * returns multiple columns (which is something different than a single record with multiple fields).

In order to return a record from the select you need to use the following syntax:

SELECT do_something( (SELECT my_table FROM my_table WHERE id = 123) );

Note that this might still fail if you don't make sure the select returns exactly one row.

If you want to apply the function to more than one row, you can do that like this:

select do_something(my_table)
from my_table;

Selecting and passing a record as a function argument

1 Answers1