FAQ
Can someone tell where can I find in SQL format users privileges for tables?

Search Discussions

  • Ben Clewett at Mar 13, 2003 at 1:33 pm
    What is the eaisest way of getting the current version number from an
    installation???

    Thanks, Ben
  • Ben Clewett at Mar 13, 2003 at 2:34 pm
    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
  • Paul butler at Mar 13, 2003 at 2:56 pm
    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)
  • Ben Clewett at Mar 13, 2003 at 3:20 pm

    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)
  • Joe Conway at Mar 13, 2003 at 3:40 pm

    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
  • Ben Clewett at Mar 13, 2003 at 3:54 pm
    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
  • Ben Clewett at Mar 13, 2003 at 4:25 pm
    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
  • Bruno Wolff III at Mar 13, 2003 at 3:09 pm

    On Thu, Mar 13, 2003 at 13:34:13 +0000, Ben Clewett 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.
  • Joe Conway at Mar 13, 2003 at 3:13 pm

    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
  • Jason Earl at Mar 13, 2003 at 6:09 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 13, '03 at 10:38a
activeMar 13, '03 at 6:09p
posts11
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase