CODEWITHSUNDEEP
postgresqlplpgsql
Wells
Wells

Reputation: 10969

Function returning single row from a table

Can't for the life of me get this function to properly return a row (it will only return one as I am querying on the primary key). It looks like this.

CREATE OR REPLACE FUNCTION sch.getrow(
    IN gy integer,
    IN gw integer,
    IN pi uuid)
RETURNS SETOF record AS $$

BEGIN

EXECUTE 'SELECT * FROM sch.foo WHERE gy = $1 AND gw = $2 AND idpi = $3' INTO record USING gy, gw, pi;

END

$$
LANGUAGE plpgsql IMMUTABLE;

Trying various RETURN EXECUTE and RETURN QUERY etc, but nada: the SQL string will be a dynamically defined string as the table name will end up changing based on the input variables.

Upvotes: 1

Views: 1629

Answers (1)

klin
klin

Reputation: 121604

If the function returns SETOF you can use RETURN QUERY regardless of how many rows (one or more) the function can return.

CREATE OR REPLACE FUNCTION sch.getrow(
    IN gy integer,
    IN gw integer,
    IN pi uuid)
RETURNS SETOF record AS $$
BEGIN
    RETURN QUERY
    EXECUTE 'SELECT * FROM sch.foo WHERE gy = $1 AND gw = $2 AND idpi = $3' USING gy, gw, pi;

END

$$
LANGUAGE plpgsql;

Not related but important. The function should not be defined as IMMUTABLE because its results depend on data stored in a table. Per the documentation:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list.

Upvotes: 1

Related Questions