Re: [SQL] Cross-table constraints
> On Tue, 28 Jan 2003, Rodger Donaldson wrote: > > > Now, adding a UNIQUE constraint on the pk for add_queue weeds out > > dupes there. However, attempting to add a cross-table UNIQUE check > > with: > > > > alter table add_queue add constraint add_queue_no_dupe_sites unique > > (sites.url); > > ERROR: parser: parse error at or near "." > > > > ,,,fails. Foreign key constraints will only require a match (of one > > sort or another) in the foreign table, according to the postgresql 7.2 > > documentation, and do not have an option to require no match. > > > > Am I missing something obvious (syntax for UNIQUE, for example), or > > trying to do something that just doesn't work that way? > > I'm not sure what a unique constraint on sites.url on table add_queue > is supposed to mean precisely. Sorry; to clarify, it's table.column notation. > You can probably come pretty close by making a set of triggers on > both tables however. That's what I feared 8). -- Rodger Donaldson [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating tables from within functions
On Fri, 24 Jan 2003, Seethalakshmi VB wrote: > Is it possible to issue a CREATE TABLE statement from inside of a PostgreSQL > function? If not, then how about from within a PL/pgSQL function? I have > tried a lot of different permutations but can't seem to get it to compile > and/or run cleanly. For example, consider: > > CREATE FUNCTION _testcreate () RETURNS text AS ' > BEGIN > CREATE TABLE switch_table2 (switch_id varchar(32), > selection_name varchar(100)); > end; > ' language 'plpgsql'; > > What is wrong with this? What type should be returned? Or perhaps it is Well you have no return value. Once I put one in it works for me. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] USING INDEX
How do we determine which column/s to be set when creating an INDEX? As I understand, using INDEX the query will be more faster then without using INDEX. How about using INDEX if query is a JOIN query, which involved 4 tables. ---(end of broadcast)--- TIP 3: 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] Cross-table constraints
On Tue, Jan 28, 2003 at 20:05:15 +1300, Rodger Donaldson <[EMAIL PROTECTED]> wrote: > > The problem: I have two tables, add_queue and sites, on a postgresql > 7.2.x database. add_queue is where items go to be reviewed by a human > before being moved into sites. One of the things I'd like to do is to > cut down on the amount of work done by humans filtering out dupes and > sundry other problems. Create a third table with three id columns. The first is the primary key and the other two tables should reference it. The second and third should allow for nulls and each reference the id field in one of the other two tables. You also need a table constraint that forces exactly one of these two fields to be null. And two more constraints to make sure they match the primary key when they aren't null. This should make sure each id is in exactly one of the two original tables and that any id in the two original tables is in the new table. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LONG - Question on dealing w/ numerics
> "David Durst" <[EMAIL PROTECTED]> writes: >> insert into journal_lines >> (entry_id,account_id,line_type,line_amount) >> values (eid,aid,ltype,amount); >> select into line * from journal_lines where entry_id = eid AND >> account_id = aid AND ltype = ltype; > > I bet that last should be line_type = ltype? Just to let you know, changing ltype to line_type fixed the problem. But I still think your point about the function selecting more than one line is valid. The problem is, the journal_line_id is not created until the insert occurs and there is no other unique ident than the journal_line_id. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
On Tue, Jan 28, 2003 at 23:39:47 -0500, Wei Weng <[EMAIL PROTECTED]> wrote: > What about a UNIQUEIDENTIFIER type? You probably want to use serial type as a replacement. Serial is really int with a default obtained using a sequence. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Filter function
Evgen Potemkin <[EMAIL PROTECTED]> writes: > term2=>select * from tab where tab=1; > ERROR: exprType: Do not know how to get type for 711 node What release is this? I get Relation reference "tab" cannot be used in an expression in 7.3 and CVS tip, and Attribute 'tab' not found in prior releases. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LONG - Question on dealing w/ numerics
"David Durst" <[EMAIL PROTECTED]> writes: > But I still think your point about the function selecting more than > one line is valid. > The problem is, the journal_line_id is not created until the insert > occurs and there is no other unique ident than the journal_line_id. Well, my standard answer to that would be "your WHERE clause should select on a primary key (which could be multiple columns)". We're pretty constrained in changing the semantics of plpgsql, because the raison d' etre of that language is to emulate Oracle's (TM) PL/SQL (TM), warts and all. (Now, if you can show us that PL/SQL behaves differently in that situation, we'll definitely be willing to change plpgsql.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] plpgsql: return results of a dynamic query
Hello,
I'am a complete newbie to plpgsql, so if this information is explained
somewhere where I haven't looked, yet, please point me to the doc... (I
didn't find the answer in the PostgreSQL Programmer's Guide.)
I use PG 7.3.1.
I'm trying to retrieve a row count from several tables (40) and would like
to create a function that does this automatically for the 40 and displays
the results. So, I loop through the tables:
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT relname AS name FROM pg_class
WHERE relkind IN ('r')
AND relname like '%_random' AND relname != 'tout_random'
LOOP
then I need to do the select count for each table in the lines of
SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN tout_random
AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE;
and return the result of each of these select counts.
Could someone indicate how to return the results of these queries ?
Am I right that in order to do this dynamic query, I have to use an
EXECUTE statement ? Can I return the results of an EXECUTE statement ?
Thanks,
Moritz
---(end of broadcast)---
TIP 3: 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] CSV import
On Wednesday 29 January 2003 5:50 am, Oliver Vecernik wrote: > Oliver Vecernik schrieb: > > Hi again! > > > > After investigating a little bit further my CSV import couldn't work > > because of following reasons: > > > > 1. CSV files are delimited with CR/LF > > 2. text fields are surrounded by double quotes > > > > Is there a direct way to import such files into PostgreSQL? Here's a simple command that will take "hello","world","splat","diddle" "he said "hello world" to ","his mate" and convert it to the following tab delimited file that can be COPYed using psql. It even handles quotes inside fields. (^m and ^i are done by typing CTRL+V CTRL+M and CTRL+V CTRL+I) hello world splat diddle he said "hello world" tohis mate sed 's/^"//' t1.txt Gary > > The answer seems to be no. But after googeling a bit a found a wonderful > Python module called csv at: > > http://www.object-craft.com.au/projects/csv/ > > A minimal script called 'csv2tab.py' for conversion to a tab delimited > file could be: > > #!/usr/bin/env python > > import csv > import sys > > def convert(file): > try: > f = open(file, 'r') > lines = f.readlines() > p = csv.parser() > for line in lines: > print '\t'.join(p.parse(line)) > except: > print 'Error opening file!' > > if __name__ == '__main__': > convert(sys.argv[1]); > > Regards, > Oliver -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: 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] plpgsql: return results of a dynamic query
>
> I'm trying to retrieve a row count from several tables (40) and would
like
> to create a function that does this automatically for the 40 and
displays
> the results. So, I loop through the tables:
>
> DECLARE
> obj RECORD;
> BEGIN
> FOR obj IN SELECT relname AS name FROM pg_class
> WHERE relkind IN ('r')
> AND relname like '%_random' AND relname != 'tout_random'
> LOOP
>
>
> then I need to do the select count for each table in the lines of
>
> SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN
tout_random
> AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE;
>
> and return the result of each of these select counts.
>
> Could someone indicate how to return the results of these queries ?
> Am I right that in order to do this dynamic query, I have to use an
> EXECUTE statement ? Can I return the results of an EXECUTE statement ?
>
You are on the right track.
The documentation says:
The results from SELECT queries are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE.
So, the only way to extract a result from a dynamically-created SELECT
is to use the FOR-IN-EXECUTE form described later.
So something like the following should do the trick:
DECLARE
obj RECORD;
obj2 RECORD;
countresult BIGINT;
BEGIN
countresult := 0;
FOR obj IN SELECT relname AS name FROM pg_class
WHERE relkind IN ('r')
AND relname like '%_random' AND relname != 'tout_random'
LOOP
FOR obj2 IN
EXECUTE ''SELECT count(t1.id) AS total FROM '' ||
quote_ident(obj.name) ||
'' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE''
LOOP
countresult := countresult + obj2.total;
END LOOP;
END LOOP;
RETURN countresult;
END;
' LANGUAGE 'plpgsql' ;
I doubt this is exactly what you wanted.
It looks like you were asking for the results of every count.
The only quick solution I can see for this is
populate a table with the name and count of your 40 tables.
Replace the "countresult := countresult + obj2.total;" line by
INSERT INTO countresults VALUES ( obj.name , obj2.total ) ;
and don't forget to reset the table before by
DELETE FROM countresults ;
I hope this helps for now.
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] plpgsql: return results of a dynamic query
>>
>> I'm trying to retrieve a row count from several tables (40) and would
> like
>> to create a function that does this automatically for the 40 and
> displays
>> the results. So, I loop through the tables:
>>
>> DECLARE
>> obj RECORD;
>> BEGIN
>> FOR obj IN SELECT relname AS name FROM pg_class
>> WHERE relkind IN ('r')
>> AND relname like '%_random' AND relname != 'tout_random'
>> LOOP
>>
>>
>> then I need to do the select count for each table in the lines of
>>
>> SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN
> tout_random
>> AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE;
>>
>> and return the result of each of these select counts.
>>
>> Could someone indicate how to return the results of these queries ?
>> Am I right that in order to do this dynamic query, I have to use an
>> EXECUTE statement ? Can I return the results of an EXECUTE statement ?
>
>>
> You are on the right track.
> The documentation says:
>
> The results from SELECT queries are discarded by EXECUTE, and SELECT
> INTO is not currently supported within EXECUTE.
> So, the only way to extract a result from a dynamically-created SELECT
> is to use the FOR-IN-EXECUTE form described later.
>
> So something like the following should do the trick:
>
> DECLARE
> obj RECORD;
> obj2 RECORD;
> countresult BIGINT;
> BEGIN
> countresult := 0;
> FOR obj IN SELECT relname AS name FROM pg_class
> WHERE relkind IN ('r')
> AND relname like '%_random' AND relname != 'tout_random'
> LOOP
> FOR obj2 IN
> EXECUTE ''SELECT count(t1.id) AS total FROM '' ||
> quote_ident(obj.name) ||
> '' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE''
>LOOP
> countresult := countresult + obj2.total;
>END LOOP;
> END LOOP;
> RETURN countresult;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> I doubt this is exactly what you wanted.
> It looks like you were asking for the results of every count.
> The only quick solution I can see for this is
> populate a table with the name and count of your 40 tables.
>
> Replace the "countresult := countresult + obj2.total;" line by
> INSERT INTO countresults VALUES ( obj.name , obj2.total ) ;
> and don't forget to reset the table before by
> DELETE FROM countresults ;
>
> I hope this helps for now.
Thanks, that was it !
The only thing I had to change was the quoting in the EXECUTE statement
(double quoting gave me a parser error). So here's the version that works
for me:
DECLARE
obj RECORD;
obj2 RECORD;
BEGIN
DELETE FROM how_many;
FOR obj IN SELECT relname AS name FROM pg_class
WHERE relkind IN ('r')
AND relname like '%_random' AND relname != 'tout_random'
LOOP
FOR obj2 IN EXECUTE 'SELECT count(t1.id) AS total FROM ' ||
quote_ident(obj.name) || ' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id
WHERE t2.ok=TRUE'
LOOP
INSERT INTO how_many VALUES ( obj.name , obj2.total ) ;
END LOOP;
END LOOP;
RETURN true;
END;
Again thanks for the clear response.
Cheers,
Moritz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] help: triggers
hello, I'm trying to update a columm on a table with a trigger but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC 2.96). when I update a row the trigger does nothing. what can I do? thanks in advance tony here is my trigger: CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''My Trigger is updating the table''; IF OLD.status=''Activo'' THEN NEW.tempo := 10; NEW.actual := now(); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER update_online BEFORE UPDATE ON iobjects FOR EACH ROW EXECUTE PROCEDURE update_my_objekt(); ## ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] help: triggers
It would be better if you could provide the source of that trigger and involved table schemas? Wei - Original Message - From: "Tony Simbine" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 29, 2003 8:29 AM Subject: [SQL] help: triggers > hello, > > I'm trying to update a columm on a table with a trigger > but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC > 2.96). > > when I update a row the trigger does nothing. > what can I do? > > thanks in advance > > tony > > > here is my trigger: > > > CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS ' > BEGIN > RAISE NOTICE ''My Trigger is updating the table''; > IF OLD.status=''Activo'' THEN > NEW.tempo := 10; > NEW.actual := now(); > END IF; > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > > CREATE TRIGGER update_online BEFORE UPDATE ON iobjects > FOR EACH ROW EXECUTE PROCEDURE update_my_objekt(); > > ## > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Inheritence and Integrity
I am creating a database that will keep track of several different types of 'events'. I am toying with the idea of making a base 'class' table for the tables because a lot of the information will be the same (also there will probably be times I just need to get the basic information about events regardless of their type). My question is: will triggers and rules on the parent table fire when I insert data in the child tables? Are there any other potential pitfalls? Thank you, -Neal Lindsay P.S. Here is a simplified example of my schema: CREATE TABLE parenttable ( recordid SERIAL PRIMARY KEY, recordname text ); CREATE TABLE childtablea ( afield int4 ) INHERITS parenttable; CREATE TABLE childtableb ( bfield text ) INHERITS parenttable; CREATE TABLE extrainfo ( extrainfoid SERIAL PRIMARY KEY, record_fkey int4 NOT NULL REFERENCES parenttable(recordid), extrainfotext text ); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inheritence and Integrity
On Wed, 29 Jan 2003, Neal Lindsay wrote: > I am creating a database that will keep track of several different types > of 'events'. I am toying with the idea of making a base 'class' table > for the tables because a lot of the information will be the same (also > there will probably be times I just need to get the basic information > about events regardless of their type). My question is: will triggers > and rules on the parent table fire when I insert data in the child > tables? Are there any other potential pitfalls? Currently that won't do what you want because triggers are not inherited and the constraint is set up so the references constraint ends up being only on the rows in parenttable. In addition, the primary key constraint won't do what you probably want either, although since it's a serial, you won't be likely to notice. > P.S. Here is a simplified example of my schema: > > CREATE TABLE parenttable ( > recordid SERIAL PRIMARY KEY, > recordname text > ); > > CREATE TABLE childtablea ( > afield int4 > ) INHERITS parenttable; > > CREATE TABLE childtableb ( > bfield text > ) INHERITS parenttable; > > CREATE TABLE extrainfo ( > extrainfoid SERIAL PRIMARY KEY, > record_fkey int4 NOT NULL REFERENCES parenttable(recordid), > extrainfotext text > ); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inheritence and Integrity
Stephan Szabo wrote: On Wed, 29 Jan 2003, Neal Lindsay wrote: I am creating a database that will keep track of several different types of 'events'. I am toying with the idea of making a base 'class' table for the tables because a lot of the information will be the same (also there will probably be times I just need to get the basic information about events regardless of their type). My question is: will triggers and rules on the parent table fire when I insert data in the child tables? Are there any other potential pitfalls? Currently that won't do what you want because triggers are not inherited and the constraint is set up so the references constraint ends up being only on the rows in parenttable. In addition, the primary key constraint won't do what you probably want either, although since it's a serial, you won't be likely to notice. So what you're saying is that I could insert a duplicate primary key into the parent table by inserting an explicit value in that field in my child table? And if I leave that column out of my insert statement the "default nextval()" will still make it a unique value? If that is so, is there a way to make constraint that will keep primary keys unique across all the child tables of my parent table? -Neal Lindsay ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inheritence and Integrity
> inheriting pk and triggers pg inheritance is quite limited. what i (and i'm sure many others) have done is: 1. create master sequence 2. create base table 3. create base trigger procedures 4. create derived tables, using "inherit" 5. write procedure p( table_name ) that a) sets pk of table_name using master sequence b) attaches base trigger procedures onto table_name 6. run procedure p() against each derived table another way to skin this cat is to use "objects" in the database: -- base table table common( int id primary key ..., ref_tab name, -- name of secondary table using common ...-- common columns and constraints ) without oids; -- secondary table table secondary1( int id1 not null references common(id), int id2 primary key, -- (can use id1 as pk!) ... -- secondary columns and constraints ) without oids; -- views for secondary table - generate! create secondary1_v1 as select c.*, s.* from secondary1 s join common c on( s.id1 = c.id ); -- (if you want) dml for view to make life easier - generate! ... if you are maintaining the common info, or if you want a many to one secondary to master, this approach is easier. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 3: 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] [HACKERS] Please include hier-patch in next PostgreSQL version
On Mon, 2003-01-27 at 10:05, Boris Klug wrote: > Hello! > > I want to say that it would be fantastic when you include the Oracle like > "CONNECT BY" patch (see gppl.terminal.ru/readme.html) in the next version of > PostgreSQL. > It is very usefull for people that have to handle such kind of hierarchical > data. Yes, very useful -- but use the SQL 99 syntax for recursive queries and not the wonky Oracle version. It's more flexible. Looking forward to the patch ;) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
