CODEWITHSUNDEEP
sqlpostgresqltypesplpgsql
inf3rno
inf3rno

Reputation: 26139

plpgsql - column type auto detect by returning a resultset

I got Postgresql 8.4

I have a user table

user_id INT,
user_name VARCHAR(255),
user_email VARCHAR(255),
user_salt VARCHAR(255)

and 2 functions:

one with SETOF:

CREATE FUNCTION test ()
  RETURNS SETOF "user"
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      *
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

one with TABLE:

CREATE FUNCTION test ()
  RETURNS TABLE (id INT, name VARCHAR, email VARCHAR)
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

By SETOF the column type is filled automatically, but I cannot set the column name and which columns to select in the result. By TABLE I can cut off the user_ prefix and set the exact column names, but I have to set the column types manually.

Is it possible to got the advantages of both?

Upvotes: 1

Views: 1628

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

Type handling with in PostgreSQL (and SQL in general) is strict. Defining the RETURN type of a function can be tricky.
There are simple solutions with plain SQL:

Select and rename all columns:

SELECT * FROM t AS t(id, name, email, salt);

Select and rename some columns:

SELECT user_id AS id, user_name AS name FROM t;

Combine with function

If you need a server side function for some reason, you can still combine these SQL features with a function. Given a table t like defined in your question and this simple function:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS SETOF t AS
$func$
SELECT * FROM t  -- do stuff here
$func$ LANGUAGE sql STABLE;

To only rename columns (no need to provide types):

SELECT * FROM f_test() t(id, name, email, salt)

Select and rename some columns:

SELECT user_id AS id, user_name AS name FROM f_test() t;

You could possibly combine this with various different functions on different tables:
Refactor a PL/pgSQL function to return the output of various SELECT queries

But I am not entirely sure which problem in particular you want to tackle here.

Asides

Upvotes: 2

inf3rno
inf3rno

Reputation: 26139

I don't think this is possible in pl/pgsql because, it strongly depends on user defined types. Sadly this language is not smart enough for type auto detection... I think my first possible solution I'll use, it solves the problem partially because at least I won't need to refactor every function manually by type change of a table column.

1.) Possible solution with asking column types:

CREATE FUNCTION test ()
  RETURNS TABLE (id "user".user_id%TYPE, name "user".user_name%TYPE, email "user".user_email%TYPE)
AS
  $BODY$
  BEGIN
    return QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

With this at least the type is not redundant. Not the best, but acceptable.

2.) Possible solution with SETOF RECORD:

CREATE FUNCTION test ()
  RETURNS SETOF RECORD
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id AS id, "user".user_name AS name, "user".user_email AS email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Without column type definition list I got the following error:

ERROR: a column definition list is required for functions returning "record"

So I have to use it like this:

SELECT * FROM test() AS (id INT, name VARCHAR, email VARCHAR);

Instead of this:

SELECT * FROM test()

I got every column in string by the php client, so the column type definition is more than useless for me... This solution would be the best without column type definition, but with it not acceptable.

It is possible to use this similar to table:

CREATE FUNCTION test (OUT id "user".user_id%TYPE, OUT name "user".user_name%TYPE, OUT email "user".user_email%TYPE)
  RETURNS SETOF RECORD
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

For example I could set everything to TEXT:

CREATE FUNCTION test (OUT id TEXT, OUT name TEXT , OUT email TEXT )
  RETURNS SETOF RECORD
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id::TEXT , "user".user_name::TEXT , "user".user_email::TEXT 
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

This works, but this is far from type auto detection, and it would result a lot of redundant text converter code...

3.) Possible solution with refcursors:

CREATE FUNCTION test ()
  RETURNS SETOF "user"
AS
  $BODY$
  DECLARE
    refc "user";
  BEGIN
    FOR refc IN 
      SELECT
        "user".user_id, "user".user_name, "user".user_email
      FROM
        "user"
    LOOP
        RETURN NEXT refc;
    END LOOP ;
    RETURN ;
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

This fills out the lacking columns with null values, I cannot name the columns, and sql loops are very slow... So this is not acceptable.

By refcursors there is another way: to return the refcursor itself, but it is not acceptable because I cannot use it as a normal variable, I have to give a string as cursor name... Btw I did not manage to use the refcursor itself as result in phpstorm. I got jdbc cursor not found error. Maybe I set the name wrong, I don't know, I don't think it's worth more effort.

Upvotes: 1

Related Questions