[SQL] POSIX Regular Expression question
Hi, I want to get out a string only with characters A-Za-z. I tried really a lot of things with substring and read many POSIX docs, I'm also familiar with the Perl RegEx but right now, I'm giving up... ;-( Any idea how to do this in Postgres with POSIX Regex? Regards, Aldor ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] POSIX Regular Expression question
am 05.09.2005, um 14:57:06 +0100 mailte Aldor folgendes: > Hi, > > I want to get out a string only with characters A-Za-z. like this? test=# select * from foo; s 123 abz abz123 (3 rows) test=# select * from foo where s ~ '^[a-zA-Z]+$'; s - abz (1 row) > Any idea how to do this in Postgres with POSIX Regex? The term '[a-zA-Z]+' means: this chars at least one times, the '^' and '$' are anchors to the begin and end of the line. Hope that helps, Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] POSIX Regular Expression question
On Mon, Sep 05, 2005 at 02:57:06PM +0100, Aldor wrote: > > I want to get out a string only with characters A-Za-z. > > I tried really a lot of things with substring and read many POSIX docs, > I'm also familiar with the Perl RegEx but right now, I'm giving up... ;-( > > Any idea how to do this in Postgres with POSIX Regex? Match the beginning of the string with ^. Match one or more characters in the set A-Za-z with [A-Za-z]+ (or with just [A-Z]+ or [a-z]+ if you're doing a case-insensitive match). Using [[:alpha:]]+ should also work. Match the end of the string with $. Examples: SELECT 'abcd' ~ '^[A-Za-z]+$'; ?column? -- t (1 row) SELECT 'ABCD' ~* '^[a-z]+$'; ?column? -- t (1 row) SELECT 'ABC123' ~* '^[a-z]+$'; ?column? -- f (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 15:57 schrieb Aldor: > I want to get out a string only with characters A-Za-z. > Any idea how to do this in Postgres with POSIX Regex? Presumably, colname ~ '^[A-Za-z]*$' If you want to be independent of locale issues, then you'd have to enumerate all the letters instead of using a range specification. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] POSIX Regular Expression question
am 05.09.2005, um 16:19:28 +0200 mailte Peter Eisentraut folgendes: > Am Montag, 5. September 2005 15:57 schrieb Aldor: > > I want to get out a string only with characters A-Za-z. > > Any idea how to do this in Postgres with POSIX Regex? > > Presumably, > > colname ~ '^[A-Za-z]*$' ^ This match also a empty string. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] POSIX Regular Expression question
On Mon, Sep 05, 2005 at 04:19:28PM +0200, Peter Eisentraut wrote: > Am Montag, 5. September 2005 15:57 schrieb Aldor: > > I want to get out a string only with characters A-Za-z. > > Any idea how to do this in Postgres with POSIX Regex? > > Presumably, > > colname ~ '^[A-Za-z]*$' > > If you want to be independent of locale issues, then you'd have to enumerate > all the letters instead of using a range specification. Note that [:alpha:] and such character classes are defined by POSIX to be locale independent: alvherre=# select 'á' ~ '[a-z]'; ?column? -- f (1 fila) alvherre=# select 'á' ~ '[[:alpha:]]'; ?column? -- t (1 fila) -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:10 schrieb A. Kretschmer: > > colname ~ '^[A-Za-z]*$' > > This match also a empty string. An empty string also fulfulls the condition "only with characters A-Za-z". Or maybe not. :-) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] POSIX Regular Expression question
In article <[EMAIL PROTECTED]>, Peter Eisentraut <[EMAIL PROTECTED]> writes: > Presumably, > colname ~ '^[A-Za-z]*$' > If you want to be independent of locale issues, then you'd have to enumerate > all the letters instead of using a range specification. How so? I thought character ranges are more an encoding than a locale issue. I dunno the details of the supported encodings, but is there any encoding where 'a-z' includes more or less than 26 letters? Fortunately we don't support EBCDIC ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:13 schrieb Alvaro Herrera: > Note that [:alpha:] and such character classes are defined by POSIX to > be locale independent: > > alvherre=# select 'á' ~ '[a-z]'; > ?column? > -- > f > (1 fila) > > alvherre=# select 'á' ~ '[[:alpha:]]'; > ?column? > -- > t > (1 fila) I don't think this addresses the concern I intended to raise. The first query should succeed for all letters between a and z, the second should succeed for all letters. Neither is guaranteed to succeed only for all "normal" Latin letters a, b, c, ... z. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:40 schrieb Harald Fuchs: > How so? I thought character ranges are more an encoding than a locale > issue. That is incorrect. > I dunno the details of the supported encodings, but is there > any encoding where 'a-z' includes more or less than 26 letters? Well, it seems that our regexp library interprets [a-z] as exactly 26 letters, but that seems to be a lack of locale support rather than a feature. There are statements in the documentation of other regexp libraries that directly contradict this behavior. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] POSIX Regular Expression question
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Am Montag, 5. September 2005 17:40 schrieb Harald Fuchs: >> I dunno the details of the supported encodings, but is there >> any encoding where 'a-z' includes more or less than 26 letters? > Well, it seems that our regexp library interprets [a-z] as exactly 26 > letters, > but that seems to be a lack of locale support rather than a feature. ISTM that the notation should properly mean "every character that sorts between a and z inclusive". So it's sort order dependent and thus locale dependent --- or at least should be. The locale support in our regexp code is definitely pretty weak at the moment. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Equivalent of Oracle SQL%NOTFOUND in plpgsql
Folks, Background: I'm an old Oracle PL/SQL guy. In PL/SQL I can write some like the following: fetch cursorblah into blurble; if cursorname%NOTFOUND . or use the more general SQL%NOTFOUND symbol that is not specific to a cursor. There are similar "easy" variables to get at the SQLSTATE code and associated error message. Question: How do I read the sqlstate for either/or the most recent SQL command or a nominated cursor from within plpgsql? I can find relevant info for C/spi stuff, but can't see it for plpgsql. Am I blind, not looking in the right spot, is it undocumented, or unimplemented? Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Equivalent of Oracle SQL%NOTFOUND in plpgsql
"Bath, David" <[EMAIL PROTECTED]> writes: >In PL/SQL I can write some like the following: > fetch cursorblah into blurble; > if cursorname%NOTFOUND . See the FOUND variable: http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Weird Trigger Behaviour using IF ... THEN ... ESLEIF... THEN... ELSEIF... THEN ... ELSE ... END IF;
Hi
Please can you help
me with the following trigger I have written in PostgreSQL 7.4.8 running
under Fedora Linux, using pgAdmin III as client.
For events in a
system, I
wanted to break up a string which is a log statement (inserted into event
table) into key-value pairs (where the string is '|' delimited) and
insert them into a separate table (event_value). I have written a trigger
to achieve this and it works fine.
However, I want to
execute certain events if the key (that is inserted into the event_value table)
matches certain criteria. To do this I wrote a another trigger, however I get very
strange behaviour. Follow this example:
I have got the
following tables:
CREATE TABLE event
( event_id int4 NOT NULL, session_id varchar(255),
event_timestamp timestamp, log_statement varchar(2000),
application varchar(30), company varchar(30), environment
varchar(30), CONSTRAINT event_id_pkey PRIMARY KEY
(event_id));
CREATE TABLE
event_value ( event_value_id int4 NOT NULL, event_id int4
NOT NULL, event_key varchar(100), event_value
varchar(2000), CONSTRAINT event_value_id_pkey PRIMARY KEY
(event_value_id), CONSTRAINT fk_event_event_value FOREIGN KEY
(event_id) REFERENCES event (event_id) ON UPDATE NO ACTION ON DELETE NO
ACTION);
CREATE TABLE
strings_tbl ( value VARCHAR(256));
I have added the
PL/pgSQL language to
the database, if you have not got this installed use the following
statements:
CREATE OR REPLACE
FUNCTION plpgsql_call_handler() RETURNS language_handler
AS'$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE 'c'
VOLATILE;
CREATE TRUSTED
PROCEDURAL LANGUAGE 'plpgsql' HANDLER
plpgsql_call_handler;
Now I create a
trigger on event table as :
CREATE OR REPLACE
FUNCTION fn_event() RETURNS "trigger" AS' DECLARE
-- Declare a variable to hold the event
id. eventid
INTEGER;
logme
VARCHAR; eventvalue_id INTEGER;
delimiter VARCHAR DEFAULT \'|\';
-- Declare a variable to key and value.
eventkey VARCHAR;
eventvalue VARCHAR;
BEGIN eventid :=
NEW.event_id; logme := substring(NEW.log_statement FROM
position(delimiter IN NEW.log_statement)+1 FOR
length(NEW.log_statement));
WHILE length(logme) > 0 AND position(delimiter IN logme) > 0
LOOP BEGIN eventkey := substring(logme FROM 0 FOR
position(delimiter IN logme)); logme := substring(logme FROM
(position(delimiter IN logme)+1) FOR length(logme)); IF
position(delimiter IN logme) = 0 THEN
BEGIN eventvalue :=
logme;
END; ELSE
BEGIN
eventvalue := substring(logme FROM 0 FOR
position(delimiter IN logme));
END; END IF; logme := substring(logme FROM
position(delimiter IN logme) + 1 FOR length(logme)); SELECT INTO
eventvalue_id nextval(\'event_sequence\'); EXECUTE \'INSERT INTO
event_value (event_value_id, event_id, event_key, event_value) VALUES (\'
|| eventvalue_id || \',\' || eventid || \',\' ||
quote_literal(eventkey) || \',\' || quote_literal(eventvalue) ||
\')\'; END; END LOOP;
RETURN NULL; END; '
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER
tg_event AFTER INSERT ON event
FOR EACH
ROW EXECUTE PROCEDURE fn_event();
Then I create a
trigger on event_value:
CREATE OR REPLACE
FUNCTION fn_event_value() RETURNS TRIGGER AS 'DECLARE
-- Declare a variable to key and value.
id
INTEGER;BEGIN EXECUTE \'INSERT INTO strings_tbl VALUES
( \' || quote_literal(NEW.event_key) || \')\'; IF
\'APPLICATION\' = NEW.event_key
THEN EXECUTE \'INSERT INTO
strings_tbl VALUES ( \' || quote_literal(NEW.event_value) ||
\')\'; RETURN
NULL; END IF; IF \'CLI\' =
NEW.event_key THEN EXECUTE
\'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_value) ||
\')\'; RETURN
NULL; END IF;
-- do nothing, nothing at all... EXECUTE \'INSERT INTO
strings_tbl VALUES ( \' || quote_literal(\'EMPTY\') || \')\';
RETURN NULL;END;' LANGUAGE plpgsql;
Create sequence
:
CREATE SEQUENCE
event_sequence INCREMENT 1 MINVALUE 1 MAXVALUE
9223372036854775807 START 360 CACHE
10;
Now if you execute the
following insert statements:
INSERT INTO event
(event_id,session_id, event_timestamp, log_statement)VALUES
(nextval('event_sequence'),'testsession1',current_timestamp,'|BROWSER|Voice
Browser|23|CLI|12334232|HOSTNAME|server name|THREAD|56|CALL_START|2005-7-29
16:32:25.875|');
I get the following
results:
event table has the
whole log statement
event_value table
has log statement broken up in key value pairs
however, in
strings_tbl table I have the following rows :
BROWSER
EMPTY
CLI
EMPTY
HOSTNAME
EMPTY
THREAD
EMPTY
CALL_START
EMPTY
What I expect is the
following:
BROWSER
EMPTY
CLI
12334232
HOSTNAME
EMPTY
THREAD
EMPTY
CALL_START
EMPTY
Now if I change the
trigger with this I get the excepted result:
CREATE OR REPLACE FUNCTION fn_event_value() RE
[SQL] Searching for results with an unknown amount of data
Hi, I've got two tables, A and B, the first one containing columns A_ID | info where A_ID is primary, so that this table stores various information about an object, and the second containing columns A_ID | property where property is an integer referring to a particular property that an object may possess. I've seperated these into two tables so that an object may have several (ie an unknown number of) properties. Now, I want to find all objects that have at most properties 1,2,3, say (so something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this -- can anyone help? Also, what if I want to find all the objects possessing properties 4,5,6, as well as possibly other things? I've done it with nested SELECTs (constructed programmatically), but I think this is quite slow and not a very pleasing solution. Obviously, both these things will need to be done for an arbitrary list of integers. Thanks, DL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Table Constraint CHECK(SELECT()) alternative?
Hello,
I have seen in another thread that sub-queries in a CHECK constraint
have implementation ramifications that make them awkward to implement and
support. OK, fair enough, c'est la vie.
ERROR: cannot use subquery in check constraint
is the result. I have a model which seems to BEG for just such a feature
though. What alternatives are there to the use of CHECK(SELECT()) in this model?
I am implementing a basic tree of nodes. There are leafs and
directories... node types. Each node in a directory has a unique name, and the
node type ids point to a table with node type names. Each node points to a
valid node (its parent). There is more... but now to my question. I wish to
constrain parent nodes to only be directories type nodes. Leafs cannot be
parents.
Table "public.raw_pnfs_nodes"
Column | Type | Modifiers
+---+-
node_id| integer | not null default
nextval('public.raw_pnfs_nodes_node_id_seq'::text)
parent_node_id | integer | not null
node_name | character varying(80) | not null
node_type_id | smallint | not null
Indexes:
"raw_pnfs_nodes_pkey" primary key, btree (node_id)
"raw_pnfs_nodes_node_name_key" unique, btree (node_name, parent_node_id)
Foreign-key constraints:
"parent_refersto_node" FOREIGN KEY (parent_node_id) REFERENCES
raw_pnfs_nodes(node_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"raw_pnfs_nodes" FOREIGN KEY (node_type_id) REFERENCES
node_types(node_type_id) ON UPDATE RESTRICT ON DELETE RESTRICT
What I WANT to write is the table constraint:
CONSTRAINT parents_are_dirs_check CHECK( (SELECT A.node_type_id FROM
raw_pnfs_nodes A, raw_pnfs_nodes B WHERE A.node_id = B.parent_node_id) = 1)
where "1" is the id for directory node types (ok, this is not polished yet)
Hopefully this illustrates the idea. The node type of a node that is a parent to another
node must be the directory node type. Is there another way to express this in SQL? I
would like to avoid putting this into the business logic layer.
Thanks much,
RDK
smime.p7s
Description: S/MIME Cryptographic Signature
[SQL] SQL queries
HiIm having problems doing some SQL queries, they are this ones:-List lock users-Show schema owners-Check if a certain table has a toast tableIf any one could help that would be good.Regards__Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
[SQL] ERROR: syntax error at or near "select" at character 9
Hi
I am using PG 8.0 on Win32, learning SQL.
I dod create a table with the following SQL:
CREATE TABLE test (
id integer,
nev varchar(25),
datum timestamp
);
A sequence with:
CREATE SEQUENCE "public"."test_azon_seq"
INCREMENT 1 MINVALUE 101
MAXVALUE 9223372036854775807 START 101
CACHE 1
;
A before insert trigger:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
NEW.id := select nextval('test_azon_seq');
NEW.nev := nev;
NEW.datum := current_timestamp;
RETURN NEW;
END;
$test_verif$ LANGUAGE plpgsql
;
CREATE TRIGGER test_verif BEFORE INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE test_verif()
;
When I issue the
insert into "public"."test" (nev) values ('Graf László');
insert statement, it returns
"ERROR: syntax error at or near "select" at character 9".
Why? What is wrong?
--
László Graf
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2 (and rename the columns)?
# [EMAIL PROTECTED] / 2005-08-31 12:00:30 +0200: > I want to copy several columns of a source table from db1 to db2, and > create the target table and rename the columns in the process. > > Is that possible in PostgresQL? If so, an example or url for such a > command /script would be appreciated... check these man pages: pg_dump(1), pg_restore(1), alter_table(7) -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] insert only if conditions are met?
Henry Ortega wrote: What I am trying to do is * Insert a record for EMPLOYEE A to TABLE A IF the sum of the hours worked by EMPLOYEE A on TABLE A is not equal to N Is this possible? Yes, but we will need to see your database schema to provide examples. A simple example might be INSERT INTO table_a SELECT firstname, lastname FROM table_b WHERE table_b IN (SELECT id FROM (SELECT id, sum(labor) as total_hours from table_c group by id) WHERE total_hours <> n); Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2 (and
Joost Kraaijeveld schrieb: On Wed, 2005-08-31 at 12:40 +0200, Roman Neuhauser wrote: check these man pages: pg_dump(1), pg_restore(1), alter_table(7) I am afraid that the problem is more complex. The original database (which is created with SQL_ASCII) contains invalid byte sequences in some columns (target database created with UNICODE): [EMAIL PROTECTED]:~/postgresql$ pg_dump -t artik munttest | psql muntfinal output.txt ERROR: invalid byte sequence for encoding "UNICODE": 0xeb207a CONTEXT: COPY artik, line 11705, column omschrijving: "Anna v. Groot Brittannië zi prf 38.61 mm" So I cannot dump/restore/alter table. I was hoping that piping the text from stdout to psql that a valid conversion to unicode would take place but apparently that is not the case. Any other ideas? If you know the implicit charset you used in your SQL_ASCII db, try with: SET client_encoding TO 'the_charset'; In your import script if you use one. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sql function: using set as argument
Found a solution Second function can be written as Get_count(anyarray, int) Select count(b_column) from some_table where some_field_1 any ($1) and some_field_2 = $2; Calling this function: Select Get_count(array(function_1), int_value) Regards, akshay --- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. (www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445 -Original Message- From: Akshay Mathur [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 10, 2005 8:00 PM To: 'pgsql-sql@postgresql.org' Subject: sql function: using set as argument Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay --- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. (www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445
[SQL] Performance issue
I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it has been 5 month since I install that server. I wonder why now my web very slow to retrieve and display data? When I check the memory, I found that postgreSQL client seem not release after allocate. I try to find bug on my script, but everything look clean to me. Anyone have experience like me.. please share info with me Thanks anyway, Ricky Sutanto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Table Constraint CHECK(SELECT()) alternative?
On Sat, 3 Sep 2005, Robert D. Kennedy wrote: > I have seen in another thread that sub-queries in a CHECK > constraint have implementation ramifications that make them awkward to > implement and support. OK, fair enough, c'est la vie. > > ERROR: cannot use subquery in check constraint > > is the result. I have a model which seems to BEG for just such a feature > though. What alternatives are there to the use of CHECK(SELECT()) in > this model? The simplest thing is to place the select into a function and use a check constraint on that (with appropriate row locks). However, that won't prevent removal or change of a referenced row. You can use triggers to prevent that (in your case to prevent removal or change of id of a referenced node or the change of type of a referenced mode away from directory). At that point, you might be just as well off using a trigger to do the initial check as well. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Table Constraint CHECK(SELECT()) alternative?
You can create a function and call it from a trigger on that column insert
event
Thanks
Dinesh Pandey
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Robert D. Kennedy
Sent: Sunday, September 04, 2005 12:20 AM
To: pgsql-sql@postgresql.org
Cc: [EMAIL PROTECTED]
Subject: [SQL] Table Constraint CHECK(SELECT()) alternative?
Hello,
I have seen in another thread that sub-queries in a CHECK constraint
have implementation ramifications that make them awkward to implement and
support. OK, fair enough, c'est la vie.
ERROR: cannot use subquery in check constraint
is the result. I have a model which seems to BEG for just such a feature
though. What alternatives are there to the use of CHECK(SELECT()) in this
model?
I am implementing a basic tree of nodes. There are leafs and
directories... node types. Each node in a directory has a unique name, and
the node type ids point to a table with node type names. Each node points to
a valid node (its parent). There is more... but now to my question. I wish
to constrain parent nodes to only be directories type nodes. Leafs cannot be
parents.
Table "public.raw_pnfs_nodes"
Column | Type |
Modifiers
+---+---
--
node_id| integer | not null default
nextval('public.raw_pnfs_nodes_node_id_seq'::text)
parent_node_id | integer | not null
node_name | character varying(80) | not null
node_type_id | smallint | not null
Indexes:
"raw_pnfs_nodes_pkey" primary key, btree (node_id)
"raw_pnfs_nodes_node_name_key" unique, btree (node_name, parent_node_id)
Foreign-key constraints:
"parent_refersto_node" FOREIGN KEY (parent_node_id) REFERENCES
raw_pnfs_nodes(node_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"raw_pnfs_nodes" FOREIGN KEY (node_type_id) REFERENCES
node_types(node_type_id) ON UPDATE RESTRICT ON DELETE RESTRICT
What I WANT to write is the table constraint:
CONSTRAINT parents_are_dirs_check CHECK( (SELECT A.node_type_id FROM
raw_pnfs_nodes A, raw_pnfs_nodes B WHERE A.node_id = B.parent_node_id) = 1)
where "1" is the id for directory node types (ok, this is not polished yet)
Hopefully this illustrates the idea. The node type of a node that is a
parent to another node must be the directory node type. Is there another way
to express this in SQL? I would like to avoid putting this into the business
logic layer.
Thanks much,
RDK
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Weird Trigger Behaviour using IF ... THEN ... ESLEIF...
On Mon, 5 Sep 2005, Antony Sohal wrote: > Please can you help me with the following trigger I have written in > PostgreSQL 7.4.8 running under Fedora Linux, using pgAdmin III as > client. > Now I create a trigger on event table as : > > CREATE OR REPLACE FUNCTION fn_event() > RETURNS "trigger" AS > ' > DECLARE > -- Declare a variable to hold the event id. > eventid INTEGER; > logme VARCHAR; > eventvalue_id INTEGER; > delimiter VARCHAR DEFAULT \'|\'; > -- Declare a variable to key and value. > eventkey VARCHAR; > eventvalueVARCHAR; > > BEGIN > eventid := NEW.event_id; > logme := substring(NEW.log_statement FROM position(delimiter IN > NEW.log_statement)+1 FOR length(NEW.log_statement)); > > WHILE length(logme) > 0 AND position(delimiter IN logme) > 0 LOOP > BEGIN > eventkey := substring(logme FROM 0 FOR position(delimiter IN logme)); > logme := substring(logme FROM (position(delimiter IN logme)+1) FOR > length(logme)); > > IF position(delimiter IN logme) = 0 THEN > BEGIN >eventvalue := logme; > END; > ELSE > BEGIN >eventvalue := substring(logme FROM 0 FOR position(delimiter IN > logme)); > END; > END IF; > logme := substring(logme FROM position(delimiter IN logme) + 1 FOR > length(logme)); > SELECT INTO eventvalue_id nextval(\'event_sequence\'); > EXECUTE \'INSERT INTO event_value (event_value_id, event_id, > event_key, event_value) VALUES (\' || > eventvalue_id || \',\' || eventid || \',\' || > quote_literal(eventkey) || \',\' || quote_literal(eventvalue) || \')\'; > END; > END LOOP; > RETURN NULL; > END; > ' > LANGUAGE 'plpgsql' VOLATILE; > > > CREATE TRIGGER tg_event AFTER INSERT ON event > FOR EACH ROW EXECUTE PROCEDURE fn_event(); > > Then I create a trigger on event_value: > > CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS ' > DECLARE > -- Declare a variable to key and value. > id INTEGER; > BEGIN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_key) || \')\'; > IF \'APPLICATION\' = NEW.event_key THEN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_value) || \')\'; > RETURN NULL; > END IF; > IF \'CLI\' = NEW.event_key THEN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_value) || \')\'; > RETURN NULL; > END IF; > > -- do nothing, nothing at all... > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(\'EMPTY\') || \')\'; > > RETURN NULL; > END; > ' LANGUAGE plpgsql; I don't see the ELSEIF in either of the above. > Now if I change the trigger with this I get the excepted result: > > CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS ' > DECLARE > -- Declare a variable to key and value. > id INTEGER; > BEGIN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_key) || \')\'; > IF \'APPLICATION\' = NEW.event_key THEN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_value) || \')\'; > RETURN NULL; > END IF; > IF \'CLI\' = NEW.event_key THEN > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(NEW.event_value) || \')\'; > RETURN NULL; > END IF; > > -- do nothing, nothing at all... > EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || > quote_literal(\'EMPTY\') || \')\'; > > RETURN NULL; > END; > ' LANGUAGE plpgsql; > > > However, please can some one explain why IF THEN ELSEIF . > THEN ELSEIF THEN ELSE END IF; doesn't work. IIRC Until 8.0 the correct spelling is ELSIF. I believe 8.0 added ELSEIF as an alternate spelling. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ERROR: syntax error at or near "select" at character 9
On Fri, 2 Sep 2005, [ISO-8859-2] Graf L?szl? wrote:
>
>CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
>BEGIN
>NEW.id := select nextval('test_azon_seq');
I think you want to remove select here, you're already effectively doing a
select of the right hand side in the assignment.
>NEW.nev := nev;
I think you want to remove this line entirely. What nev were you
expecting on the right hand side? If it's the new one, well, NEW.new is
already that.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Searching for results with an unknown amount of data
On Sep 2, 2005, at 2:40 PM, DownLoad X wrote: Hi, I've got two tables, A and B, the first one containing columns A_ID | info where A_ID is primary, so that this table stores various information about an object, and the second containing columns A_ID | property where property is an integer referring to a particular property that an object may possess. I've seperated these into two tables so that an object may have several (ie an unknown number of) properties. Now, I want to find all objects that have at most properties 1,2,3, say (so something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this -- can anyone help? Also, what if I want to find all the objects possessing properties 4,5,6, as well as possibly other things? I've done it with nested SELECTs (constructed programmatically), but I think this is quite slow and not a very pleasing solution. Obviously, both these things will need to be done for an arbitrary list of integers. Thanks, DL Without knowing more about your data or schema, couldn't you do something like SELECT A_ID, property FROM "B" WHERE property IN ( 1, 2, 3 ); To accommodate arbitrary lists, I can't imagine how you'd do it without using a PL, but you could still build your IN clause programmatically. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
