Re: [SQL] function parameters : bug?
Richard Hayward wrote: pg 8.0.3 This behaviour seems odd to me: CREATE TABLE mytable ( inta INTEGER ); CREATE OR REPLACE FUNCTION myfunction (inta integer) RETURNS integer AS $body$ begin insert into mytable(inta) values (inta); ERROR: syntax error at or near "$1" at character 22 QUERY: insert into myTable( $1 ) values ( $2 ) CONTEXT: PL/pgSQL function "myfunction" line 2 at SQL statement The problem is with the line in my myfunction: insert into mytable(inta) values (inta); The server doesn't like the field name being the same as the name of the function parameter. I don't see why, as I can see no opportunity for ambiguity. So what would this do? SELECT inta+1 FROM mytable Agreed, it's tricky, but without some variable marker (e.g. $my_param) or removing inline SQL from the language, I'm not sure there's much to do. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Possible to use a table to tell what table to select from?
Frank Hagstrom wrote: Hello I've been thinking on a potential problem I might get in a distant future, but once I started thinking on it I just as well had to check... Is it possible to have say ~ 6 tables, where the first one is a 'reference' to all the others (they all are with the same structure). The other 5 or so have information stored in importance/access/age, where one table will be only recent/important things, the other with a bit lower priority and so on until the last table that can be really huge and slow and used more like a storage ... Is it possible to write an SQL query to let the first table figure out what table one will do the actual select from? If the tables are all holding the same sort of thing, e.g. logs2003, logs2004, logs2005 etc. then you might want to look at partitioning. This is being looked at for the next version, check the archives of the hackers list. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] getting back autonumber just inserted
On Thu, Jul 07, 2005 at 14:47:23 -0600, Larry Meadors <[EMAIL PROTECTED]> wrote: > If you have a trigger on your table that inserts a record in a table > and shares the same sequence, what value do you get back, the > triggered curval, or the currently inserted one? Whichever one was done second. I think it has to be the trigger, because I think the inserted values will be evaluated first (even before aa before trigger) since the triggers need to have access to them. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] problem (bug?) with "in (subquery)"
I have the following query that isn't behaving like I would expect: select * from strains s where s.id in (select strain_id from pathway_strains); I would expect each strain record to appear only once. Instead I get output like this, where the same strain id appears many times: id | name | organism ---+--+-- 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 506 | common | 487 506 | common | 487 ... continues By the way, this output is the same as if running the query: select * from strains s join pathway_strains ps on ps.strain_id = s.id; = Table "public.strains" Column | Type |Modifiers ---+--+- id| integer | not null name | text | not null default 'common'::text organism | integer | not null Indexes: "strains_pkey" PRIMARY KEY, btree (id) == View "public.pathway_strains" Column | Type | Modifiers +-+--- pathway_id | integer | strain_id | integer | View definition: SELECT DISTINCT p.id AS pathway_id, c.strain_id FROM catalyst_associations c JOIN pathway_edges e ON c.pathway_edge_id = e.id RIGHT JOIN pathways p ON p.id = e.pathway_id ORDER BY p.id, c.strain_id; The contents of pathways_strains are like pathway_id | strain_id +--- 2083 |76 2083 |80 2083 |83 2083 |95 2084 |76 2084 |80 2084 |83 2084 |95 2084 | 162 ...etc So, am I wrong in expecting each strain record to appear only once in the result set? Or is there something wrong with PostgreSQL? I would be happy to provide more information if it's needed. Thank you! Luca ps: # select version(); version - PostgreSQL 8.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] How to obtain the list of data table name only
Hi, I am wondering if anyone can tell me how I can obtain only the list of data table in postgresql without function and other ancillary tables. I hope that I can add a tag that can filter only data table. I am using the following SQL Statement: "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" I would appreciate if anyone can enligten me on how this can be achieve. Sunny ---(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] Yearly Archival System Using PostgreSQL
I am developing a database system using PostgreSQL in which I should be able to delete redundant records once a year (Time specified by the user). THe redundant records should be inserted into the archive table which is part of the same database. Is this possible in pgsql? Can I program a function in SQL or a trigger to fire when the computer switches on say Jan 1 of every year? Will be grateful if anybody could point me in the right direction. Vivek ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] difference between all RDBMSs
hello sir, i m an engineering student and i m looking out for differrences between oracle v/s sybase v/s sql v/s plsql v/s mysql with respect to the following points 1. rdbms 2. data independence 3. support of plsql 4. integrity 5. security 6. use and application 7. support to thirdparty products 8. functionability 9. operatability 10. reliability please reply me as soon as possible. Dhanashree Dhanashree__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] [postgres] Betrifft: Datumsfeld leer lassen
--- In [EMAIL PROTECTED], Stefan Miefert <[EMAIL PROTECTED]> schrieb: > Hallo, > > was muss ich angeben wenn ich ein undefiniertes Datumsfeld schreiben möchte > > Wenn ic datum ='' angebe meckert er:) Bin zwar selber noch DAU, aber Tipp eins: - mit ALTER TABLE tabelle COLUMN date SET NULL; (Versionsabhängig) Tipp zwei: ändere den Datentyp von deiner Spalte date auf einen Wert, der NULL akzeptiert (varchar o.ä.) mfg Marco Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Yahoo! Groups Links <*> Besuchen Sie Ihre Group im Web unter: http://de.groups.yahoo.com/group/postgres/ <*> Um sich von der Group abzumelden, senden Sie eine Mail an: [EMAIL PROTECTED] <*> Mit der Nutzung von Yahoo! Groups akzeptieren Sie unsere: http://de.docs.yahoo.com/info/utos.html ---(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] Help With complex join
Hi all, got a question as how to approach a somewhat complicated join query. The deal is I have three tables called attorney, lawOffice, and law_office_employment. The attorney and lawOffice tables hold attorney and lawOffice information respectively (obviously). The law_office_employment table is meant to show historical periods of time for which the attorney's worked for the different lawOffices. Here is the create table statement for law_office_employment: /*==*/ /* Table: LAW_OFFICE_EMPLOYMENT */ /*==*/ create table LAW_OFFICE_EMPLOYMENT ( ATTORNEYID IDENTIFIER not null, LAWOFFICEID IDENTIFIER not null, STARTDATEDATE not null, constraint PK_LAW_OFFICE_EMPLOYMENT primary key (ATTORNEYID, LAWOFFICEID, STARTDATE) ); /*==*/ /* Index: LAW_OFFICE_EMPLOYMENT_PK */ /*==*/ create unique index LAW_OFFICE_EMPLOYMENT_PK on LAW_OFFICE_EMPLOYMENT ( ATTORNEYID, LAWOFFICEID, STARTDATE ); /*==*/ /* Index: RELATION_46_FK*/ /*==*/ create index RELATION_46_FK on LAW_OFFICE_EMPLOYMENT ( ATTORNEYID ); /*==*/ /* Index: RELATION_48_FK*/ /*==*/ create index RELATION_48_FK on LAW_OFFICE_EMPLOYMENT ( LAWOFFICEID ); alter table LAW_OFFICE_EMPLOYMENT add constraint FK_LAW_OFFI_RELATION__ATTORNEY foreign key (ATTORNEYID) references ATTORNEY (ATTORNEYID) on delete restrict on update restrict; alter table LAW_OFFICE_EMPLOYMENT add constraint FK_LAW_OFFI_RELATION__LAW_OFFI foreign key (LAWOFFICEID) references LAWOFFICE (LAWOFFICEID) on delete restrict on update restrict; I can populate the law_office_employment so that it looks like this: attorneyid | lawofficeid | startdate +-+ 1 | 1 | 2002-01-01 1 | 2 | 2002-02-01 1 | 1 | 2002-03-01 1 | 3 | 2002-04-01 My question is how to make a query that will display the PERIODS of time for which an attorney worked for a particular office based on the attorney then changing to a new law office and having the endDate of the previous employment be the startDate of the new employment. I know it sounds confusing but as an example I will show you what I would want the query to return based on the information populated above. attorneyid | lawofficeid | startdate | enddate +-++--- 1 | 1 | 2002-01-01 | 2002-02-01 1 | 2 | 2002-02-01 | 2002-03-01 1 | 1 | 2002-03-01 | 2002-04-01 1 | 3 | 2002-04-01 | Present I am pretty sure it involves joining the table with itself to cet the cartesian product of all of the rows but I am at a loss for how to construct the interval logic. Any help would be greatly appreciated. Sean Pinto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Error on dynamic code.
I am trying to write a dynamic trigger function on insert operations so that the same function can be used across all my tables. In this case my tables are ‘test’ and ‘test_a’ (my convention is that all audit table names are the name of the original table concatenated with ‘_a’). Below is part of my code in plpgsql: CREATE OR REPLACE FUNCTION audit_insert() RETURNS "trigger" AS $BODY$ DECLARE new_audit_row RECORD; dynamic_SQL text; BEGIN --Instantiate new_audit_row to the required type. dynamic_SQL := 'SELECT INTO new_audit_row * ' || 'FROM ' || quote_ident(TG_RELNAME || '_a') || ';'; EXECUTE dynamic_SQL; --... more code here END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; However, when I try to insert data in ‘test’ I am getting the following error message: ERROR: syntax error at or near "INTO" at character 8 QUERY: SELECT INTO new_audit_row * FROM "test_a"; CONTEXT: PL/pgSQL function "audit_insert" line 18 at execute statement The funny thing is that the documentation I read about SELECT INTO and RECORD types give the following example, amongst others: DECLARE users_rec RECORD;BEGIN SELECT INTO users_rec * FROM users WHERE user_id=3; --...more codeEND; (full code can be found at http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT ) Which is basically identical (except for the WHERE clause) to the query returned in the my error message!! Can anyone help please? Regs, Mark J Camilleri
Re: [SQL] Possible to use a table to tell what table to select from?
On 7/14/05, Richard Huxton wrote: > Frank Hagstrom wrote: > > Hello > > > > I've been thinking on a potential problem I might get in a distant > > future, but once I started thinking on it I just as well had to > > check... > > > > Is it possible to have say ~ 6 tables, where the first one is a > > 'reference' to all the others (they all are with the same structure). > > The other 5 or so have information stored in importance/access/age, > > where one table will be only recent/important things, the other with a > > bit lower priority and so on until the last table that can be really > > huge and slow and used more like a storage ... Is it possible to write > > an SQL query to let the first table figure out what table one will do > > the actual select from? > > If the tables are all holding the same sort of thing, e.g. logs2003, > logs2004, logs2005 etc. then you might want to look at partitioning. > This is being looked at for the next version, check the archives of the > hackers list. > > -- >Richard Huxton >Archonet Ltd > Ah ok, thanks I'll look into the partitioning then :) (not really logs, but will work in the same way, as I'm planning to use it to hold revisions, but it will have the same format in the tables...) /Frank H ---(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] Help With complex join
On 14 Jul 2005 14:34:02 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote:Hi all, got a question as how to approach a somewhat complicated join query. The deal is I have three tables called attorney, lawOffice, andlaw_office_employment. The attorney and lawOffice tables hold attorneyand lawOffice information respectively (obviously). Thelaw_office_employment table is meant to show historical periods of time for which the attorney's worked for the different lawOffices. Here isthe create table statement for law_office_employment:/*==*//* Table: LAW_OFFICE_EMPLOYMENT *//*==*/create table LAW_OFFICE_EMPLOYMENT (ATTORNEYID IDENTIFIER not null,LAWOFFICEID IDENTIFIER not null,STARTDATEDATE not null,constraint PK_LAW_OFFICE_EMPLOYMENT primary key (ATTORNEYID,LAWOFFICEID, STARTDATE));/*==*//* Index: LAW_OFFICE_EMPLOYMENT_PK *//*==*/create unique index LAW_OFFICE_EMPLOYMENT_PK on LAW_OFFICE_EMPLOYMENT (ATTORNEYID, LAWOFFICEID,STARTDATE);/*==*//* Index: RELATION_46_FK*//*==*/create index RELATION_46_FK on LAW_OFFICE_EMPLOYMENT (ATTORNEYID);/*==*/ /* Index: RELATION_48_FK*//*==*/create index RELATION_48_FK on LAW_OFFICE_EMPLOYMENT (LAWOFFICEID);alter table LAW_OFFICE_EMPLOYMENT add constraint FK_LAW_OFFI_RELATION__ATTORNEY foreign key(ATTORNEYID) references ATTORNEY (ATTORNEYID) on delete restrict on update restrict;alter table LAW_OFFICE_EMPLOYMENT add constraint FK_LAW_OFFI_RELATION__LAW_OFFI foreign key (LAWOFFICEID) references LAWOFFICE (LAWOFFICEID) on delete restrict on update restrict;I can populate the law_office_employment so that it looks like this: attorneyid | lawofficeid | startdate +-+ 1 | 1 | 2002-01-01 1 | 2 | 2002-02-01 1 | 1 | 2002-03-01 1 | 3 | 2002-04-01My question is how to make a query that will display the PERIODS oftime for which an attorney worked for a particular office based on theattorney then changing to a new law office and having the endDate of the previous employment be the startDate of the new employment. I knowit sounds confusing but as an example I will show you what I would wantthe query to return based on the information populated above. attorneyid | lawofficeid | startdate | enddate+-++--- 1 | 1 | 2002-01-01 | 2002-02-01 1 | 2 | 2002-02-01 | 2002-03-01 1 | 1 | 2002-03-01 | 2002-04-01 1 | 3 | 2002-04-01 | PresentTry this, select t1.*,(select t2.startdate from LAW_OFFICE_EMPLOYMENT t2 where t2.attorneyid=t1.attorneyid and t2.startdate > t1.start_date order by t2.startdate limit 1) from LAW_OFFICE_EMPLOYMENT t1; I am pretty sure it involves joining the table with itself to cet thecartesian product of all of the rows but I am at a loss for how to construct the interval logic.Any help would be greatly appreciated.Sean Pinto---(end of broadcast)---TIP 5: don't forget to increase your free space map settings -- with regards,S.GnanavelSatyam Computer Services Ltd.
