Stored procedure with execute and returning clause
Hi
I am having difficulty with returning clause and stored procedure. This is
an (edited) example of where I am
CREATE OR REPLACE PROCEDURE public.arrcopy1(
dataarr anyarray,
tblname text,
cols text DEFAULT NULL::text,
selstr text DEFAULT NULL::text,
INOUT outarr text[] DEFAULT NULL
)
LANGUAGE 'plpgsql'
AS $BODY$
insstr:= INSERT INTO tagdata(fileid,tagname,tagvalue) SELECT
arr[1]::integer,arr[2]::text,string_to_array(arr[3],E'\b') FROM
(select array_agg(v order by rn) arr
from unnest($1) with ordinality v(v,rn)
group by (rn - 1) / array_length($1::text[],2)
) a
JOIN tagfile ON fileid=arr[1]::int RETURNING *::text[];
Then called as
EXECUTE insstr INTO outarr USING (dataarr) ;
$BODY$
This compiles as a proc
But I then get an error (this is in perl)
DBD::Pg::db selectall_arrayref failed: ERROR: malformed array literal:
"3182753"
DETAIL: Array value must start with "{" or dimension information
The procedure works perfectly without the INTO Clause on execute
If I change returning clause to
RETURNING array[fileid]
It runs but only returns the first fileid not all fileids inserted
thanks
Re: Stored procedure with execute and returning clause
ne 23. 8. 2020 v 14:36 odesílatel Mike Martin napsal:
>
> Hi
> I am having difficulty with returning clause and stored procedure. This is
> an (edited) example of where I am
>
> CREATE OR REPLACE PROCEDURE public.arrcopy1(
> dataarr anyarray,
> tblname text,
> cols text DEFAULT NULL::text,
> selstr text DEFAULT NULL::text,
> INOUT outarr text[] DEFAULT NULL
> )
> LANGUAGE 'plpgsql'
> AS $BODY$
>
> insstr:= INSERT INTO tagdata(fileid,tagname,tagvalue) SELECT
> arr[1]::integer,arr[2]::text,string_to_array(arr[3],E'\b') FROM
> (select array_agg(v order by rn) arr
> from unnest($1) with ordinality v(v,rn)
> group by (rn - 1) / array_length($1::text[],2)
> ) a
> JOIN tagfile ON fileid=arr[1]::int RETURNING *::text[];
>
> Then called as
>
> EXECUTE insstr INTO outarr USING (dataarr) ;
> $BODY$
>
> This compiles as a proc
>
> But I then get an error (this is in perl)
>
> DBD::Pg::db selectall_arrayref failed: ERROR: malformed array literal:
> "3182753"
> DETAIL: Array value must start with "{" or dimension information
>
> The procedure works perfectly without the INTO Clause on execute
>
> If I change returning clause to
> RETURNING array[fileid]
>
> It runs but only returns the first fileid not all fileids inserted
>
I afraid so expression like '*'::text[] is not supported
you need to assign returning value to RECORD value, and then manually build
a array from array' fields
There is not any cast record to array.
Regards
Pavel
>
> thanks
>
>
>
>
has_database_privilege is true?
Hi,
I create a role "test" and it can't (intentionally) login. But why does
has_database_privilege() return true if "test" can't connect to the cluster and
can't use any database? Is this a bug or am I doing something strange here? Any
ideas?
I'm on PostgreSQL 12.4.
Cheers,
Paul
postgres=# create role "test";
CREATE ROLE
postgres=# \du test
List of roles
Role name | Attributes | Member of
---+--+---
test | Cannot login | {}
postgres=# select has_database_privilege('test', 'postgres', 'connect');
has_database_privilege
t
(1 row)
postgres=# select has_database_privilege('test', 'db01', 'connect');
has_database_privilege
t
(1 row)
...
postgres=# select datname from pg_database;
datname
---
db02
template0
template1
postgres
db01
(5 rows)
Re: has_database_privilege is true?
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > I create a role "test" and it can't (intentionally) login. But why does > has_database_privilege() return true if "test" can't connect to the cluster > and can't use any database? Is this a bug or am I doing something strange > here? Any ideas? The role attribute system (where you see 'cannot login') is largely independent from the GRANT system (which is what has_database_privilege is checking). Both are required for a user to log in. Thanks, Stephen signature.asc Description: PGP signature
Re: has_database_privilege is true?
Hi Stephen, > On 23. Aug, 2020, at 16:28, Stephen Frost wrote: > > The role attribute system (where you see 'cannot login') is largely > independent from the GRANT system (which is what has_database_privilege > is checking). Both are required for a user to log in. I see. So I need to postgres=# revoke all privileges on database "postgres", "db01", "db02" from public; REVOKE and then select something like: postgres=# select postgres-# c.datname, postgres-# b.rolname, postgres-# ( postgres(# b.rolcanlogin and postgres(# has_database_privilege(b.rolname, c.datname, 'connect') postgres(# ) as use_db postgres-# from postgres-# pg_catalog.pg_roles b, postgres-# pg_catalog.pg_database c postgres-# where postgres-# not c.datistemplate and postgres-# c.datname != 'postgres' and postgres-# b.rolname ~ '^xxx-' postgres-# order by postgres-# 2, 1, 3; datname | rolname | use_db -+-+ db01| xxx-a | f db02| xxx-a | f db01| xxx-b | t db02| xxx-b | f db01| xxx-c | f db02| xxx-c | f db01| xxx-d | f db02| xxx-d | f db01| xxx-e | f db02| xxx-e | f (10 rows) to get it right? At least the result look like expected now. Thanks very much. Cheers, Paul
Re: has_database_privilege is true?
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 23. Aug, 2020, at 16:28, Stephen Frost wrote: > > > > The role attribute system (where you see 'cannot login') is largely > > independent from the GRANT system (which is what has_database_privilege > > is checking). Both are required for a user to log in. > > I see. So I need to > > postgres=# revoke all privileges on database "postgres", "db01", "db02" from > public; > REVOKE If you want has_database_privilege() to return that the user doesn't have access due to missing GRANT privileges, yes. An alternative would be to query against pg_roles and check the 'rolcanlogin' privilege/column. Again, a user has to have both in order to be able to actually log in. Thanks, Stephen signature.asc Description: PGP signature
Re: has_database_privilege is true?
Hi Stephen, > On 23. Aug, 2020, at 16:52, Stephen Frost wrote: > > If you want has_database_privilege() to return that the user doesn't > have access due to missing GRANT privileges, yes. > > An alternative would be to query against pg_roles and check the > 'rolcanlogin' privilege/column. Again, a user has to have both in order > to be able to actually log in. which I did (see previous post). Thanks very much for clearing that up. Cheers, Paul
