1

I'm trying to call a polymorphic function where the table type can be determined by a subsidiary function, say, which_table(). The problem is that I can't convert the returned varchar into an actual type

I'm trying to base a solution off of Erwin Brandstetter's "Various complete table types" section of one of his previous SO answers, as well as his comments in the answer to SQL Injection-safe call of polymorphic function. So, referencing examples there, the behavior I want is to be able to do
SELECT * FROM data_of(NULL::pcdmet, 17);
but be able to specify the table name dynamically, such as,

SELECT * FROM data_of( which_table('arg that evaluates totypeNULL::pcdmet') , 17 )

In my case, the pcdmet, "table" types can be designed to be either all regular tables, temp tables, or composite types (so a solution using any of these would be fully acceptable).


Issue #1

I've been trying to use a PREPARE/EXECUTE approach as suggested, but haven't been able to see what I'm doing wrong.

create or replace function fooprep (inint int, inchar varchar) RETURNS varchar
AS $$
begin
RETURN ''||inint||'  '||inchar;
end;
$$ language plpgsql;


dev_db=> create type int_char as (coli int, colv varchar);
CREATE TYPE

dev_db=> prepare fooplan (int_char) as
select * from fooprep($1, $2);
ERROR:  function fooprep(int_char, unknown) does not exist
LINE 2: select * from fooprep($1, $2);

Issue #2

But further more, even if I get #1 to work, how to I return it as a type from which_table() when I don't know which table that function will return? I've tried specifying regclass as the return type for a stub which_table() that just returns a constant NULL::testtable but that doesn't seem to be usable as a data type (I'd expected to be able to use it as a table type)

I've also tried something along the lines of

create or replace FUNCTION foofn (bar anyelement DEFAULT EXECUTE fooplan_mod(5))

but get an error there, too:

ERROR:  syntax error at or near "fooplan"
LINE 1: ...ce FUNCTION foofn (bar anyelement DEFAULT EXECUTE fooplan_mod(5)...
                                                             ^

I've tried a plethora of other things to the point that I've pretty much abandoned keyword capitalization (as you can see :-). I feel like I must be close but overlooking something.

I'm using PostgreSQL 13: psql (13.9 (Ubuntu 13.9-1.pgdg20.04+1), server 13.10 (Ubuntu 13.10-1.pgdg20.04+1))

Randall
  • 2,859
  • 1
  • 21
  • 24

1 Answers1

0

Try the following approach using polymorphic types:

CREATE FUNCTION selectit(anycompatible, bigint)
 RETURNS SETOF anycompatible
 LANGUAGE plpgsql
AS $$BEGIN
   RETURN QUERY EXECUTE
      format('SELECT * FROM %s LIMIT %s',
             pg_typeof($1),
             $2);
END;$$;

Here is a test:

CREATE TABLE test (id integer PRIMARY KEY, value text);

INSERT INTO test VALUES (1, 'one'), (2, 'two'), (3, 'three');

SELECT * FROM selectit(NULL::test, 2);

 id │ value 
════╪═══════
  1 │ one
  2 │ two
(2 rows)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • (1/3) -- Thanks - that part (`SELECT * FROM selectit(NULL::test, 2);`) I think I understand. Where I'm specifically getting stuck is how to pass in the `NULL::test` as a dynamic parameter. Say I have table `test2 (roman text, arabic int)` with `VALUES ('I', 1), (V, 5), (X, 10)`. Then, I want a function called `which_table(user_friendly_arg text)` that would return ... – Randall Feb 17 '23 at 20:59
  • (2/3) -- ... return type `NULL::test` as the value for `which_table('from_arabic')`, and ... – Randall Feb 17 '23 at 21:03
  • (3/3) -- ... return type `NULL::test2` as the value for `which_table('from_roman')`. So that I would be able to do `selectit(which_table('from_roman'),2)`. *(I've broken this comment up into three pieces to try to help clarify the functionality I want)* – Randall Feb 17 '23 at 21:06
  • (4/3) -- Thus I want `selectit(` **`which_table('from_roman')`** `, 2 )` to *first* evaluate the inner function `which_table(...)` to `NULL::test2` (a *type*, not a varchar) and then to process the outer function (using that `NULL::test2` result) as `selectit(NULL::test2, 2)` which should return back the first two rows of `test2` where the column types are reversed from `test`. – Randall Feb 17 '23 at 21:23
  • You cannot get that. The requirement is that the database server must be able to determine the data type of the result at the time that the query is planned. That's what the trick with `anycompatible` does: the result type must be the same as the argument type. If you pass the table name as `text`, the planner cannot do that. So any attempt to do that is doomed. All you could do then is to return `SETOF record`, which would force you to describe the result type in the SQL statement that calls the function. In short: there is no way to get a convenient dynamic result type other than I showed. – Laurenz Albe Feb 19 '23 at 05:34