Internal function returning pg_statistic

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.

it will not be first

Pavel

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