Thread: array UNNESTed to rows stable with respect to order?
Hi all
I have created following statement to get the ordered parameter list of
functions. I use UNNEST to get rows from array. This works fine but I am
not sure whether the ordering remains in the later use. Background is
PL/pgSQL to revoke grants to get a pristine start for granting. As the
order of the parameter is important, I should use a statement that
returns the properly ordered list of parameters. Maybe I did take a
wrong turn and one can achieve this simpler.
Suggestions are very welcome.
Kind regards Thiemo
with PRO_UNNESTED_TYPES as(
select
oid as PROOID,
PRONAME,
unnest(PROARGTYPES) as PROARGTYPE,
PRONAMESPACE,
PROOWNER
from
PG_CATALOG.PG_PROC
) select
P.PRONAME,
string_agg(
T.TYPNAME,
', '
) as PARAMETER_LIST_STRING,
G.GRANTEE
from
PRO_UNNESTED_TYPES P
inner join PG_CATALOG.PG_TYPE T on
P.PROARGTYPE = T.OID
inner join PG_CATALOG.PG_NAMESPACE N on
P.PRONAMESPACE = N.OID
inner join INFORMATION_SCHEMA.ROUTINE_PRIVILEGES G on
-- copied from INFORMATION_SCHEMA.ROUTINE_PRIVILEGES source
-- as seen in DBeaver 4.3.2
(
(
P.PRONAME::text || '_'::text
)|| P.PROOID::text
)::INFORMATION_SCHEMA.SQL_IDENTIFIER = G.SPECIFIC_NAME
where
N.NSPNAME = current_user
and G.GRANTEE != current_user
group by
P.PROOID,
P.PRONAME,
G.GRANTEE
order by
P.PRONAME asc,
G.GRANTEE asc,
PARAMETER_LIST_STRING asc;
--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC
Attachment
This works fine but I am not sure whether the ordering remains in the later use.
It does not. If the array is not naturally ordered you will want to attach a "with ordinality" clause to it for performing future ordering.
select * from unnest(ARRAY[3,6,4]::integer[]) with ordinality
Use LATERAL to move the unnest from the select-list section to the FROM clause.
David J.
On 04/17/2018 01:20 PM, Thiemo Kellner wrote:
> I use UNNEST to get rows from array. This works fine but I am
> not sure whether the ordering remains in the later use.
I think you are looking for `WITH ORDINALITY` (in pg 9.4+). For instance
you could rewrite your first CTE like so:
SELECT oid as PROOID,
PRONAME,
t as PROARGTYPE,
i,
PRONAMESPACE,
PROOWNER
FROM PG_CATALOG.PG_PROC,
UNNEST(PROARGTYPES) WITH ORDINALITY AS proargtypes(t, i)
;
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Re: array UNNESTed to rows stable with respect to order?
From
"Thiemo Kellner, NHC Barhufpflege"
Date:
Zitat von "David G. Johnston" <david.g.johnston@gmail.com>: > ?It does not. If the array is not naturally ordered you will want to > attach a "with ordinality" clause to it for performing future ordering. Thanks for the hints. Kind regards -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Zitat von Paul Jungwirth <pj@illuminatedcomputing.com>: > I think you are looking for `WITH ORDINALITY` (in pg 9.4+). For > instance you could rewrite your first CTE like so: Thanks for the hint. Kind regards -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.