pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with DROP ROLE
4+ messages / 3 participants
[nested] [flat]
* Problem with DROP ROLE
@ 2011-10-19 10:11 Brice André <brice@famille-andre.be>
2011-10-19 11:32 ` Re: Problem with DROP ROLE Andreas Kretschmer <andreas@a-kretschmer.de>
2011-10-19 11:59 ` Re: Problem with DROP ROLE Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 2 replies; 4+ messages in thread
From: Brice André @ 2011-10-19 10:11 UTC (permalink / raw)
To: pgsql-sql
Hello everyone,
I would want to implement an SQL query where I would be able to suppress all
information from a registered user. I am currenlty able to suppress
everything except the user role. The name of the role is present in a table
and so, I would want to perform something like this :
DROP ROLE (SELECT ...)
but this is not considered as valid, as DROP ROLE is expecting a name and
not a text field. So, I tried the following, but with no success :
DROP ROLE CAST((SELECT...) AS name)
So, does someone knows how to handle this problem ?
Regards,
Brice
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with DROP ROLE
2011-10-19 10:11 Problem with DROP ROLE Brice André <brice@famille-andre.be>
@ 2011-10-19 11:32 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2011-10-19 12:19 ` Re: Problem with DROP ROLE Brice André <brice@famille-andre.be>
1 sibling, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2011-10-19 11:32 UTC (permalink / raw)
To: "Brice André" <brice@famille-andre.be>; pgsql-sql
You can't do that in THAT way, but you can use dynamic SQL:
test=# select * from drop_role ;
t
--------
foobar
(1 row)
test=*# do $$ declare r text; begin for r in select t from drop_role loop
execute 'drop role ' || quote_ident(r) || ';'; end loop; end; $$language
plpgsql;
DO
"Brice André" <brice@famille-andre.be> hat am 19. Oktober 2011 um 12:11
geschrieben:
> Hello everyone,
>
> I would want to implement an SQL query where I would be able to suppress all
> information from a registered user. I am currenlty able to suppress everything
> except the user role. The name of the role is present in a table and so, I
> would want to perform something like this :
> DROP ROLE (SELECT ...)
> but this is not considered as valid, as DROP ROLE is expecting a name and not
> a text field. So, I tried the following, but with no success :
> DROP ROLE CAST((SELECT...) AS name)
>
> So, does someone knows how to handle this problem ?
>
> Regards,
> Brice
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with DROP ROLE
2011-10-19 10:11 Problem with DROP ROLE Brice André <brice@famille-andre.be>
2011-10-19 11:32 ` Re: Problem with DROP ROLE Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2011-10-19 12:19 ` Brice André <brice@famille-andre.be>
0 siblings, 0 replies; 4+ messages in thread
From: Brice André @ 2011-10-19 12:19 UTC (permalink / raw)
To: pgsql-sql
Thanks very much. I replaced my statement by the following plpgsql code :
statement := 'DROP ROLE "'||(SELECT ...)||'";';
execute(statement);
and it works like a charm.
Regards,
Brice
2011/10/19 Andreas Kretschmer <andreas@a-kretschmer.de>
> You can't do that in THAT way, but you can use dynamic SQL:
>
> test=# select * from drop_role ;
> t
> --------
> foobar
> (1 row)
>
> test=*# do $$ declare r text; begin for r in select t from drop_role loop
> execute 'drop role ' || quote_ident(r) || ';'; end loop; end; $$language
> plpgsql;
>
> DO
>
>
>
>
> "Brice André" <brice@famille-andre.be> hat am 19. Oktober 2011 um 12:11
> geschrieben:
>
>
> > Hello everyone,
> >
> > I would want to implement an SQL query where I would be able to suppress
> all
> > information from a registered user. I am currenlty able to suppress
> everything
> > except the user role. The name of the role is present in a table and so,
> I
> > would want to perform something like this :
> > DROP ROLE (SELECT ...)
> > but this is not considered as valid, as DROP ROLE is expecting a name and
> not
> > a text field. So, I tried the following, but with no success :
> > DROP ROLE CAST((SELECT...) AS name)
> >
> > So, does someone knows how to handle this problem ?
> >
> > Regards,
> > Brice
> >
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with DROP ROLE
2011-10-19 10:11 Problem with DROP ROLE Brice André <brice@famille-andre.be>
@ 2011-10-19 11:59 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 4+ messages in thread
From: Guillaume Lelarge @ 2011-10-19 11:59 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
On Wed, 2011-10-19 at 12:11 +0200, Brice André wrote:
> Hello everyone,
>
> I would want to implement an SQL query where I would be able to suppress all
> information from a registered user. I am currenlty able to suppress
> everything except the user role. The name of the role is present in a table
> and so, I would want to perform something like this :
> DROP ROLE (SELECT ...)
> but this is not considered as valid, as DROP ROLE is expecting a name and
> not a text field. So, I tried the following, but with no success :
> DROP ROLE CAST((SELECT...) AS name)
>
> So, does someone knows how to handle this problem ?
>
You can't do it with only one query. You need to use two queries: the
SELECT, and then the DROP.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-19 12:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-19 10:11 Problem with DROP ROLE Brice André <brice@famille-andre.be>
2011-10-19 11:32 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2011-10-19 12:19 ` Brice André <brice@famille-andre.be>
2011-10-19 11:59 ` Guillaume Lelarge <guillaume@lelarge.info>
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