Postgres Pro
Downloads
Home   >   mailing lists

Thread: User Privilege table

User Privilege table

From
Yovko Ilchev Yovkov
Date:
Can someone tell where can I find in SQL format users privileges for tables?

Version Number

From
Ben Clewett
Date:
What is the eaisest way of getting the current version number from an
installation???

Thanks,  Ben

CHECK constraint

From
Ben Clewett
Date:
In SQL standards, I belive a SELECT query is valid in a check constraint:

CREATE TABLE foo (
    a INT CHECK ( a IN ( SELECT b FROM bar WHERE .... ) )
}

However, this seems not to be the case (yet) in PostgreSQL.

Should I do this with Triggers instead?  Are there any other elegent
methods of doing the same?

Ben

Re: CHECK constraint

From
"paul butler"
Date:
Date sent:          Thu, 13 Mar 2003 14:35:40 +0000
From:               Ben Clewett <B.Clewett@roadrunner.uk.com>
Copies to:          pgsql-novice@postgresql.org
Subject:            [NOVICE] CHECK constraint

Ben,
Would foreign keys not be the simplest solution?

CREATE TABLE foo(

id int4 NOT NULL,
FOREIGN KEY (id) REFERENCES bar(id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,

)
In SQL standards, I belive a SELECT query is valid in a check constraint:

CREATE TABLE foo (
    a INT CHECK ( a IN ( SELECT b FROM bar WHERE .... ) )
}


> However, this seems not to be the case (yet) in PostgreSQL.
>
> Should I do this with Triggers instead?  Are there any other elegent
> methods of doing the same?


Ben


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Version Number

From
Bruno Wolff III
Date:
On Thu, Mar 13, 2003 at 13:34:13 +0000,
  Ben Clewett <B.Clewett@roadrunner.uk.com> wrote:
>
> What is the eaisest way of getting the current version number from an
> installation???

For the backend use a select version() query.
For pgsql use the --version option.

Re: Version Number

From
Joe Conway
Date:
Ben Clewett wrote:
>
> What is the eaisest way of getting the current version number from an
> installation???
>

regression=# select version();
                                                   version
------------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.2 20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

HTH,

Joe

Re: CHECK constraint

From
Ben Clewett
Date:
paul butler wrote:
> Date sent:          Thu, 13 Mar 2003 14:35:40 +0000
> From:               Ben Clewett <B.Clewett@roadrunner.uk.com>
> Copies to:          pgsql-novice@postgresql.org
> Subject:            [NOVICE] CHECK constraint
>
> Ben,
> Would foreign keys not be the simplest solution?

Not in my case unfortunatelly.  I need a CHECK on a subset of referenced
values:

(in this case where 'live = true', mine's a bit more complex...)

CREATE TABLE foo (
    id int4 NOT NULL CHECK (
        id IN ( SELECT id FROM bar WHERE live = true ) ),
    FOREIGN KEY (id) REFERENCES bar (id)
)

I believe this is not (yet) possible in our favorite SQL, although part
of SQL1999.  Is this therefore only available through a TRIGGER, or
maybe there is a more elegent method?

Like a FK to a VIEW:

CREATE VIEW v_bar
    SELECT * from BAR WHERE live=true

Then my table def becomes:

CREATE TABLE foo (
    id int4 NOT NULL,
    FOREIGN KEY (id) REFERENCES v_bar (id)
)

Is this possible??  Should I cut-and-run here and do the coding in
application space?

Ben



>
> CREATE TABLE foo(
>
> id int4 NOT NULL,
> FOREIGN KEY (id) REFERENCES bar(id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>
> )
> In SQL standards, I belive a SELECT query is valid in a check constraint:
>
> CREATE TABLE foo (
>     a INT CHECK ( a IN ( SELECT b FROM bar WHERE .... ) )
> }
>
>
>
>>However, this seems not to be the case (yet) in PostgreSQL.
>>
>>Should I do this with Triggers instead?  Are there any other elegent
>>methods of doing the same?
>
>
>
> Ben
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: CHECK constraint

From
Joe Conway
Date:
Ben Clewett wrote:
> Is this possible??  Should I cut-and-run here and do the coding in
> application space?

How 'bout:

CREATE TABLE bar (
   id int4 NOT NULL,
   live bool
);

insert into bar values(1,'t');
insert into bar values(2,'f');

CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bool AS'
   SELECT live FROM bar WHERE id = $1
' language 'sql' STABLE STRICT;

CREATE TABLE foo (
   id int4 NOT NULL CHECK (check_bar(id))
);

regression=# insert into foo values(1);
INSERT 1336840 1
regression=# insert into foo values(2);
ERROR:  ExecInsert: rejected due to CHECK constraint "foo_id" on "foo"

HTH,

Joe

Re: CHECK constraint

From
Ben Clewett
Date:
Bloody-A that's perfect!

Elegent, readable, controlable.  And, unlike the CHECK ( foo IN ( SELECT
.. ), it'd dynamic as well...

Ben

Joe Conway wrote:
> Ben Clewett wrote:
>
>> Is this possible??  Should I cut-and-run here and do the coding in
>> application space?
>
>
> How 'bout:
>
> CREATE TABLE bar (
>   id int4 NOT NULL,
>   live bool
> );
>
> insert into bar values(1,'t');
> insert into bar values(2,'f');
>
> CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bool AS'
>   SELECT live FROM bar WHERE id = $1
> ' language 'sql' STABLE STRICT;
>
> CREATE TABLE foo (
>   id int4 NOT NULL CHECK (check_bar(id))
> );
>
> regression=# insert into foo values(1);
> INSERT 1336840 1
> regression=# insert into foo values(2);
> ERROR:  ExecInsert: rejected due to CHECK constraint "foo_id" on "foo"
>
> HTH,
>
> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: CHECK constraint

From
Ben Clewett
Date:
Just a final comment...

You return a 'bool' from your funtion by virtue that the one and only
value found is a 'bool'??

If the query retures no, more than one, or a mixture of 'true' and
'false' the result might be unpredictable...  If I understand the syntax.

Would it be safer to use:

CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bigint AS'
   SELECT count(*) FROM bar WHERE id = $1 AND live = 't'
' language 'sql' STABLE STRICT;

CREATE TABLE foo (
   id int4 NOT NULL CHECK (check_bar(id) != 0)
);

???

Ben


Joe Conway wrote:
> Ben Clewett wrote:
>
>> Is this possible??  Should I cut-and-run here and do the coding in
>> application space?
>
>
> How 'bout:
>
> CREATE TABLE bar (
>   id int4 NOT NULL,
>   live bool
> );
>
> insert into bar values(1,'t');
> insert into bar values(2,'f');
>
> CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bool AS'
>   SELECT live FROM bar WHERE id = $1
> ' language 'sql' STABLE STRICT;
>
> CREATE TABLE foo (
>   id int4 NOT NULL CHECK (check_bar(id))
> );
>
> regression=# insert into foo values(1);
> INSERT 1336840 1
> regression=# insert into foo values(2);
> ERROR:  ExecInsert: rejected due to CHECK constraint "foo_id" on "foo"
>
> HTH,
>
> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Version Number

From
Jason Earl
Date:
Simply connect to the server via psql (or whatever client you prefer),
and do:

SELECT version();

The output should be something like this:

template1=> select version();
                                               version

-----------------------------------------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030308 (Debian prerelease)
(1 row)


Ben Clewett <B.Clewett@roadrunner.uk.com> writes:

> What is the eaisest way of getting the current version number from an
> installation???
>
> Thanks,  Ben
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html