dblink: add polymorphic functions.
| От | Corey Huinker |
|---|---|
| Тема | dblink: add polymorphic functions. |
| Дата | |
| Msg-id | CADkLM=d9AEZYQ2TpzOJQwBb42nV49YQy6b6S=z4q9svJiQLB-g@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: dblink: add polymorphic functions.
(Michael Paquier <michael.paquier@gmail.com>)
Re: dblink: add polymorphic functions. (Merlin Moncure <mmoncure@gmail.com>) |
| Список | pgsql-hackers |
In the course of writing a small side project which hopefully will make its way onto pgxn soon, I was writing functions that had a polymorphic result set.
Inside that function would be multiple calls to dblink() in both synchronous and async modes. It is a requirement of the function that each query return a result set conforming to the structure passed into p_row_type, but there was no way for me to express that.create function foo( p_row_type anyelement, p_param1 ...) returns setof anyelement
Unfortunately, there's no way to say
select * from dblink_get_result('connname') as <polymorphic record type>;
Instead, I had to generate the query as a string like this.
Moreover, I'm now executing this string dynamically, incurring reparsing and replanning each time (and if all goes well, this would be executed many times). Granted, I could avoid that by rewriting the stored procedure in C and using prepared statements (not available in PL/PGSQL), but it seemed a shame that dblink couldn't itself handle this polymorphism.with x as (select a.attname || ' ' || pg_catalog.format_type(a.atttypid, a.atttypmod) as sql_textfrom pg_catalog.pg_attribute awhere a.attrelid = pg_typeof(p_row_type)::text::regclassand a.attisdropped is falseand a.attnum > 0order by a.attnum )select format('select * from dblink_get_result($1) as t(%s)',string_agg(x.sql_text,','))from x;
So with a little help, we were able to come up with polymorphic set returning dblink functions.
Below is the results of the patch applied to a stock 9.4 installation.
[local]:ubuntu@dblink_test# create extension dblink;CREATE EXTENSIONTime: 12.778 ms[local]:ubuntu@dblink_test# \df dblinkList of functionsSchema | Name | Result data type | Argument data types | Type--------+--------+------------------+---------------------------------+--------public | dblink | SETOF record | text | normalpublic | dblink | SETOF anyelement | text, anyelement | normalpublic | dblink | SETOF record | text, boolean | normalpublic | dblink | SETOF anyelement | text, boolean, anyelement | normalpublic | dblink | SETOF record | text, text | normalpublic | dblink | SETOF anyelement | text, text, anyelement | normalpublic | dblink | SETOF record | text, text, boolean | normalpublic | dblink | SETOF anyelement | text, text, boolean, anyelement | normal(8 rows)[local]:ubuntu@dblink_test# select * from dblink('dbname=dblink_test','select * from pg_tables order by tablename limit 2',null::pg_tables);schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers------------+--------------+------------+------------+------------+----------+-------------pg_catalog | pg_aggregate | postgres | | t | f | fpg_catalog | pg_am | postgres | | t | f | f(2 rows)Time: 6.813 ms
Obviously, this is a trivial case, but it shows that the polymorphic function works as expected, and the code that uses it will be a lot more straightforward.
Proposed patch attached.
Вложения
В списке pgsql-hackers по дате отправления: