Thread: Internal function returning pg_statistic

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:


{ oid => '3434',
   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.

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,

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

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