pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG: value in information_schema.parameters.parameter_default is always NULL for roles t
2+ messages / 2 participants
[nested] [flat]
* BUG: value in information_schema.parameters.parameter_default is always NULL for roles t
@ 2024-06-09 10:44 Vedran Bilopavlović <vbilopav@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Vedran Bilopavlović @ 2024-06-09 10:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
A program produces the wrong output for any given input:
Values in table *information_schema.parameters*, column *parameter_default*,
are always NULL only for roles that are not superuser.
The same column returns the correct values for superuser roles.
There is a workaround by using pg_proc table:
select
unnest(proargnames[pronargs-pronargdefaults+1:pronargs] )optargnames,
unnest(string_to_array(pg_get_expr(proargdefaults, 0)::text,','))
optargdefaults
from
pg_catalog.pg_proc
where
proname = 'proc name'
However, this approach relies on CSV parsing and will return wrong results
when the parameter default contains a comma.
--
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG: value in information_schema.parameters.parameter_default is always NULL for roles t
@ 2024-06-09 17:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vedran Bilopavlović <vbilopav@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-06-09 17:06 UTC (permalink / raw)
To: Vedran Bilopavlović <vbilopav@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
=?UTF-8?Q?Vedran_Bilopavlovi=C4=87?= <vbilopav@gmail.com> writes:
> Values in table *information_schema.parameters*, column *parameter_default*,
> are always NULL only for roles that are not superuser.
I see no bug there. The manual defines parameter_default as
The default expression of the parameter, or null if none or if the
function is not owned by a currently enabled role.
A superuser is considered to own everything, but for mere-mortal
roles a result would only appear for functions you own. The
SQL implementation of the view appears to match this definition:
CASE WHEN pg_has_role(proowner, 'USAGE')
THEN pg_get_function_arg_default(p_oid, (ss.x).n)
ELSE NULL END
You could certainly quibble with the value of hiding the information
when it's readily available from our system catalogs --- but this
is a SQL-standard-defined view so we implement it as the standard
says, and this is what the standard says it should do.
> There is a workaround by using pg_proc table:
I'd recommend using something from the pg_get_function*() family.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-09 17:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-09 10:44 BUG: value in information_schema.parameters.parameter_default is always NULL for roles t Vedran Bilopavlović <vbilopav@gmail.com>
2024-06-09 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox