Обсуждение: 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

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

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.