Re: Internal function returning pg_statistic - Mailing list pgsql-hackers
From | Kyotaro Horiguchi |
---|---|
Subject | Re: Internal function returning pg_statistic |
Date | |
Msg-id | 20191121.123230.2248917130997878199.horikyota.ntt@gmail.com Whole thread Raw |
In response to | Re: Internal function returning pg_statistic (Pavel Stehule <pavel.stehule@gmail.com>) |
List | pgsql-hackers |
At Wed, 20 Nov 2019 11:26:16 +0100, Pavel Stehule <pavel.stehule@gmail.com> wrote in > Hi > > st 20. 11. 2019 v 10:59 odesílatel Konstantin Knizhnik < > k.knizhnik@postgrespro.ru> napsal: > > > Hi hackers, > > > > Working on global temporary table I need to define function which > > returns set of pg_statistic records. > > Unfortunately I failed to declare such function! > > Type pg_statistic is defined in postgres.bki so I was not able to refer > > to it in pg_proc.dat file. > > And if I explicitly enumerate columns of this type: > > > > > you can define your function in postgres.bki. Mmm. AFAIK it's the old practice. Nowadays we edit pg_proc.dat. > > { oid => '3434', We are encouraged to use OIDs in the range 8000-9999 for development. unused_oids should have suggested some OID above 8000 to you. > > descr => 'show local statistics for global temp table', > > proname => 'pg_gtt_statistic_for_relation', provolatile => 'v', > > proparallel => 'u', > > prorettype => 'record', proretset => 't', proargtypes => 'oid', > > proallargtypes => > > > > '{oid,oid,int2,bool,float4,int4,float4,int2,int2,int2,int2,int2,oid,oid,oid,oid,oid,oid,oid,oid,oid,oid,_float4,_float4,_float4,_float4,_float4,anyarray,anyarray,anyarray,anyarray,anyarray}', > > proargmodes => > > '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', > > proargnames => > > > > '{relid,starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct,stakind1,stakind2,stakind3,stakind4,stakind5,staop1,staop2,staop3,staop4,staop5,stacoll1,stacoll2,stacoll3,stacoll4,stacoll5,stanumbers1,stanumbers2,stanumbers3,stanumbers4,stanumbers5,stavalues1,stavalues2,stavalues3,stavalues4,stavalues5}', > > prosrc => 'pg_gtt_statistic_for_relation' }, > > > > then I go the following error when try to use this function: > > > > a column definition list is required for functions returning "record" > > at character 111 > > > > The column definition list provided in pg_proc.dat was rejected because > > it contains reference to anyarray which can not be resolved. Yeah, the reason for the error is anyarray in proallargtypes, which prevents the record from resolved as a composite type since any hint for the type is given. If one additional INPUT argument is allowed, you can define the funtion as follows. {... proargtypes => 'oid anyarray', proallargtypes => '{oid,anyarray,oid,int2,bool,float4,int4,...}', proargmodes => '{i,i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,...}', proargnames => '{relid,type,starelid,staattnum,stainherit,...}', The second argument tells parser the returning type for the anyarrays. I think I saw the same technic somewhere in core but I don't recall. select * from pg_gtt_statistic_for_relation(1262, NULL::anyarray) limit 1; starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stak ind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | st aop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumber s1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | staval ues2 | stavalues3 | stavalues4 | stavalues5 ----------+-----------+------------+-------------+----------+-------------+----- -----+----------+----------+----------+----------+--------+--------+--------+--- -----+--------+----------+----------+----------+----------+----------+---------- ---+-------------+-------------+-------------+-------------+------------+------- -----+------------+------------+------------ (0 rows) Or you could hide anyarray in a new type. > > If I try to declare function in system_views.sql as returning setof > > pg_statistic then I got error "cannot change return type of existing > > function". > > > > CREATE OR REPLACE FUNCTION > > pg_gtt_statistic_for_relation(relid oid) returns setof pg_statistic > > LANGUAGE INTERNAL STRICT > > AS 'pg_gtt_statistic_by_relation'; > > > > And if I try to declare it as returning record and explicitly cast it to > > pg_statistic, then reported error is "cannot cast type record to > > pg_statistic". > > > > So the only possible way I found is to create extension and define > > function in this extension. > > I wonder if there is some better solution? > > > > Thanks in advance, regards. -- Kyotaro Horiguchi NTT Open Source Software Center
pgsql-hackers by date: