Обсуждение: automatical grant - role membership

On Wed, Nov 13, 2013 at 4:26 PM, Petr Suk <petrxsuk@gmail.com> wrote:
> Hi,
> I am trying to avoid neediness of manual settings of privileges for new
> tables and views for roles in the same membership.
> Let's say I have one DB schema (myschema) and two DB users (roles: role1,
> role2). "Role1" and "role2" are in mebership of role "mygroup".
> I want my DB to work like this:
> "role1" create some table in "myschema" and is the owner of this table. I
> want "role2" to automatically have all privileges on this table.

Making role2 inheriting from role1?

Luca

How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...

thanks

tg

 

Thara Vadakkeveedu <tharagv@yahoo.com> wrote:

> I tried this from pg_admin, but I get a syntax error (unexpected
> character)

>
> IF EXISTS (SELECT 0 FROM pg_class
>              WHERE relkind = 'S'
>                AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
>   THEN
>     RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
> ENF IF;

First, is this code in a plpgsql context (like a function
definition or a DO command)?  If not, that IF is not going to work.
 If it *is*, you seem to have a typo where you meant END IF.

Also note that if you force the oid to text for the comparison, it
is both more fragile and slower than if you convert the text to
regclass for the comparison.

explain analyze
SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence');
                                              QUERY PLAN                                              
-------------------------------------------------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1)
   Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text))
   Rows Removed by Filter: 295
 Total runtime: 0.221 ms
(4 rows)

explain analyze
SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
                                                         QUERY
PLAN                                                         

-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1
loops=1)
   Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid)
   Filter: (relkind = 'S'::"char")
 Total runtime: 0.062 ms
(4 rows)

Notice that there was a table scan, which was slower, and the
record was not found because the cast of the oid to regclass and
then text did not include the 'public." part.  The other way used
an index and matched as you probably intended.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"First, is this code in a plpgsql contex"?

 

No, that is my problem.

 

Does it have to be inside a Create function block or can just wrapping the if  with a BEGIN END; suffice ?

 

By itself this sql works:

SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;

 

However when I create a function for it and run it I see an error

 

create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
             WHERE relkind = 'S'
             AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass

  THEN
        return 1;
  ELSE
 return 0;
    END IF;

END;
$$ language plpgsql;

 

 

select chk_sequence();
ERROR:  operator does not exist: oid = text
LINE 3:              AND oid = ('public.' || quote_ident('hibernate_...
                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT EXISTS (SELECT 1 FROM pg_class
             WHERE relkind = 'S'
             AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass

 

Thanks.

Thara Vadakkeveedu <tharagv@yahoo.com> wrote:

> By itself this sql works:

> SELECT 0 FROM pg_class
>              WHERE relkind = 'S'
>                AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
>
> However when I create a function for it and run it I see an error
>
> create function chk_sequence() returns integer as $$
> BEGIN
> IF EXISTS (SELECT 1 FROM pg_class
>              WHERE relkind = 'S'
>              AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
>   THEN
>         return 1;
>   ELSE
>  return 0;
>     END IF;
> END;
> $$ language plpgsql;

Move the cast to regclass inside one level of parentheses.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company