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 to
type
NULL::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))