Hi,
Trying to fix a regression test problem I've left for better days while
developping the extensions, some help is needed.
The pg_execute_from_file() function is designed to work with either a
filename as its sole argument, or the filename and a VARIADIC text list
of arguments containing placeholder names and values. It works fine with
two entries in pg_proc using the same backend function, and it looks
like the following from a psql shell:
List of functions Schema | Name | Result data type | Argument data
types| Type
------------+----------------------+------------------+---------------------+--------pg_catalog | pg_execute_from_file
|void | text | normalpg_catalog | pg_execute_from_file | void | text, VARIADIC
text| normal
(2 rows)
Now the opr_sanity check includes the following query, which is expected
not to return any row:
=# SELECT p1.oid, p1.proname, p2.oid, p2.proname
-# FROM pg_proc AS p1, pg_proc AS p2
-# WHERE p1.oid < p2.oid AND
-# p1.prosrc = p2.prosrc AND
-# p1.prolang = 12 AND p2.prolang = 12 AND
-# (p1.proisagg = false OR p2.proisagg = false) AND
-# (p1.prolang != p2.prolang OR
(# p1.proisagg != p2.proisagg OR
(# p1.prosecdef != p2.prosecdef OR
(# p1.proisstrict != p2.proisstrict OR
(# p1.proretset != p2.proretset OR
(# p1.provolatile != p2.provolatile OR
(# p1.pronargs != p2.pronargs);oid | proname | oid | proname
------+----------------------+------+----------------------3927 | pg_execute_from_file | 3928 | pg_execute_from_file
(1 row)
Oops. I'm not granted to do it this way. So I've been trying to setup
pg_proc.h with a single entry and the default arguments. That's a weird
thing in there, pg_node_tree. So I've tried to copy/paste what I get
from pg_proc when I create a function in SQL with the same prototype:
create or replace function foo(text, variadic text[] default '{}'::text[]) returns text language sql
as $$ select $1 || coalesce(',' || (select array_to_string(array_agg(x), ',') from unnest($2) x), '')
$$;
({CONST :consttype 1009 :consttypmod -1 :constlen -1 :constbyval false :constisnull false :location 61 :constvalue 16 [
640 0 0 0 0 0 0 0 0 0 0 25 0 0 0 ]})
Then initdb says FATAL: cannot accept a value of type pg_node_tree.
So, should I fix the opr_sanity check, and if so, what would be the
right approach? Or should we get the proargdefaults supported in the
bootstrap mode somehow? Or should I create the function in a SQL script
that initdb will use, somewhere?
Of course having a single entry in pg_proc without default values for
the placeholders won't fly, because the user is expected to be able to
actually use the 1-argument version of the function (no placeholder).
And I don't think having 2 names is a great answer, but if it comes to
that, of course, it's easy to do.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support