[SQL] undefined relations in pg_locks
Hi there, I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" are null. May I suppose there were references to temporary objects ? However, how cand I get the related objects involved in this lock ? TIA, Sabin ---(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] undefined relations in pg_locks
Sabin Coanda wrote: Hi there, I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" are null. May I suppose there were references to temporary objects ? However, how cand I get the related objects involved in this lock ? Maybe they're not objects of the database you're connected to. You need to check the "database" column of pg_locks. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Create Table xtest (like xtype)
I know I can create a table using names and types from an existing table. Create Table xtable (a Integer, b Text); Create Table xtest (Like xtable); This works just fine. I want to create a table having the same column names and types as an existing new datatype. Create Type xtype As (a Integer, b Text); Create Table xtest (Like xtype); ERROR: "xtype" is a composite type Is that possible some other way? TJ O'Donnell http://www.gnova.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Columns view? (Finding column names for a table)
Hi, I see this documentation item but can't figure out how to use it: http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html The view columns contains information about all table columns (or view columns) in the database. However, if I execute "select columns;" I get a not found error. I'm sure there's some simple explanation - I'm interested specifically in listing the column names of a specific table (in Pg 8.2). The manual SQL I've developed is (where [table_name] is the table I want columns for): select pg_attribute.attname, * from pg_attribute join pg_class on pg_class.oid = pg_attribute.attrelid where pg_class.relname = '[table_name]' and attnum > 0 and atttypid > 0 Not pretty but seems to work. Of course if there were a view that encapsulated this and future-proofed it, that'd be much nicer. Any assistance is appreciated! Thanks, Steve ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Columns view? (Finding column names for a table)
Steve Midgley wrote on 06.02.2008 21:33: Hi, I see this documentation item but can't figure out how to use it: http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html The view columns contains information about all table columns (or view columns) in the database. select column_name from information_schema.columns where table_name = 'table_name' works for me. Thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Double query
Simple question - but my brain is not working:
This is my table
Column | Type |
Modifiers
---++-
id| integer| not null default
nextval('emr_clinicschedule_id_seq'::regclass)
clinic_id | integer| not null
date | date |
day | smallint |
status| boolean| not null
open | time without time zone |
close | time without time zone |
reason| character varying(64) |
active| boolean| not null
I want to find any value:
SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s'
But I want to make sure the clinic exist first then find if the date
exist 2nd.
How do I do that?
Thanks in advance,
J
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Information schema permissions
Assume a standard install on Linux (what ever that means). Would a normal user have access to information schema? I have a front end written in python that accesses tables and permissions are set based on roles. But I do nothing with respect to the information schema view. I would like my program to access the view. Would there be anything preventing a standard user from 'SELECT' access? -- John Fabiani ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Double query
On Wed, 06 Feb 2008 16:01:09 -0500 PostgreSQL Admin <[EMAIL PROTECTED]> wrote: > I want to find any value: > > SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s' > > But I want to make sure the clinic exist first then find if the date > exist 2nd. I'm not sure what you mean by "first" and "second" but if you mean that you want a list of all clinics that match the clinic ID and you want to know of all of those which ones have the specified date then perhaps this is what you want. SELECT id, date = '%s' FROM schedule WHERE clinic_id = '%s'; -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] outer join issues
Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? Thanks in advance. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] outer join issues
Tom Hart wrote: Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? My "I looked at this for 20 seconds" guess is that the following clauses are messing you up. > AND share.dataset = 0 > AND draft.dataset = 0 The LEFT OUTER JOIN isn't helping you if you're still comparing values in the JOINed tables in the WHERE clause. Colin ---(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] outer join issues
Tom Hart wrote: Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; try AND (share.dateset = 0 OR share.dataset IS NULL) AND (draft.dataset = 0 OR draft.dataset IS NULL) because when the left join is utilized, the dateset field will be a null, which is not =0 and hence would fail the AND clause in your version Terry Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? Thanks in advance. ---(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] outer join issues
Colin Wetherbee wrote: Tom Hart wrote: Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? My "I looked at this for 20 seconds" guess is that the following clauses are messing you up. > AND share.dataset = 0 > AND draft.dataset = 0 The LEFT OUTER JOIN isn't helping you if you're still comparing values in the JOINed tables in the WHERE clause. Colin Thank you for the responses everybody. I actually had somebody in the irc channel help me figure this one out. For the record here's my complete query (there's more additions, like a couple CASE's to translate numbers to strings) SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, CASE WHEN sh_stat_cd = 0 THEN 'Open' ELSE 'Closed' END as sh_stat_cd, COALESCE(df_balance::text, 'No Draft'), CASE WHEN df_stat_cd = 0 THEN 'Open' ELSE 'Closed' END as df_stat_cd FROM loan LEFT OUTER JOIN (select * from share where dataset = 0) as share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN (select * from draft where dataset = 0) as draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND sh_balance IS NOT NULL ; -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(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] Create Table xtest (like xtype)
"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > Create Type xtype As (a Integer, b Text); > Create Table xtest (Like xtype); > ERROR: "xtype" is a composite type Hmmm ... as you see, that's disallowed, but offhand it seems like it wouldn't be very hard to allow it. It might be as simple as relaxing the relkind check. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Information schema permissions
johnf <[EMAIL PROTECTED]> writes: > Assume a standard install on Linux (what ever that means). Would a normal > user have access to information schema? I have a front end written in python > that accesses tables and permissions are set based on roles. But I do > nothing with respect to the information schema view. I would like my program > to access the view. Would there be anything preventing a standard user > from 'SELECT' access? The information schema views are accessible to anyone, but be aware that they show different sets of rows to different people --- you can only see rows that are about tables that you have some access permissions for. This might or might not confuse your app ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Create Table xtest (like xtype)
Considering how related Created Types and Tables are (at least conceptually) it seems like a good idea to allow Created Types to be used in the Like clause. At least it would help me and make maintaining my db easier and cleaner. TJ O'Donnell http://www.gnova.com/ > "TJ O'Donnell" <[EMAIL PROTECTED]> writes: >> Create Type xtype As (a Integer, b Text); >> Create Table xtest (Like xtype); >> ERROR: "xtype" is a composite type > > Hmmm ... as you see, that's disallowed, but offhand it seems like it > wouldn't be very hard to allow it. It might be as simple as relaxing > the relkind check. > > regards, tom lane > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] accounting schema
Hi, I am learning my way into Accounting and was wondering how Accounting applications are designed. perhaps you could point the way On one hand, accountants talk about a sacret equation A = L + OE (Asset = Libility + Owner Equity) and then under each categories there are one or many account. On the other hand a DBA thinks in terms of tables and relations. Instead of getting theoritical, allow me to setup an example Say you have have construction project (like a room addition) or one of those flip this house deals Owner brings the land (equity) of say worth $100K Expenses begin to mount ( that is a minus against OE) Account Payble begins to mount (that is a liability) And one day you experience a sale As a DBA, (and keeping it simple) I am thinking I need a table for every account which migh look like id, description, credit, debit, validated, created_on, created_by, modified_on, modified_by Is that pretty match it ? Please let me know if you have seen some accounting or DB book that addresses this problem domain. Thanks Medi
Re: [SQL] Information schema permissions
On Wednesday 06 February 2008 03:37:22 pm Tom Lane wrote: > johnf <[EMAIL PROTECTED]> writes: > > Assume a standard install on Linux (what ever that means). Would a > > normal user have access to information schema? I have a front end > > written in python that accesses tables and permissions are set based on > > roles. But I do nothing with respect to the information schema view. I > > would like my program to access the view. Would there be anything > > preventing a standard user from 'SELECT' access? > > The information schema views are accessible to anyone, but be aware that > they show different sets of rows to different people --- you can only > see rows that are about tables that you have some access permissions > for. This might or might not confuse your app ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org Thanks. Actually it would benefit my app. -- John Fabiani ---(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] accounting schema
At 05:09 PM 2/6/2008, [EMAIL PROTECTED] wrote: Date: Wed, 6 Feb 2008 17:08:54 -0800 From: "Medi Montaseri" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: accounting schema Message-ID: <[EMAIL PROTECTED]> Hi, I am learning my way into Accounting and was wondering how Accounting applications are designed. perhaps you could point the way On one hand, accountants talk about a sacret equation A = L + OE (Asset = Libility + Owner Equity) and then under each categories there are one or many account. On the other hand a DBA thinks in terms of tables and relations. Instead of getting theoritical, allow me to setup an example Hi Medi, You might read some source code and docs for open source accounting software and see "how it's done." Here's one example that might be interesting and simple enough to follow: http://www.gnucash.org/ In general, I think there are many different accounting methods, so you have to get clear about which one you're using. "Double-entry" accounting is common. Cash vs. accrual posting methods matter (i.e. when does an expense or receivable "charge" against the assets balance?) My most basic understanding is that in general you track assets as they come in, to an "Accounts Receivable" ledger (i.e. table) and liabilities to an "Accounts Payable" ledger. Then you reconcile these two "books" into a "General Ledger" table which gives you something like an audit trail of all activity (and a running balance). I'm sure Wikipedia will define these three terms and lots more with greater clarity. But my (limited) experience with accounting schema is that they often involve these three tables (AR/AP/GL) at their core. As you add bank accounts, complex investment instruments, depreciation etc, things get considerably more complex of course. I'll readily admit my limited experience, and I'm sure others on this list have far better information. I hope this gets you started anyway. Sincerely, Steve ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] accounting schema
Thanks Steve... This is all well and good...I am getting it...but I need to chew on it moregnucash was a good one...didn't think of thatgot to get passed all the GUI stuff...but...excellent ref... I suppose instead of AR and AP tables, I can just have one table called Entry (thanks Joe) with an attribute indicating AR vs AP. I am also in favor of Double Entry and accrual postingwhat do I know, I hear corporations do it this way... I also like the "audit trail" built-in feature...actually this was something I was baffled about...I was originally thinking about editing capability, but what you are suggesting is that in practice, you don't change or erase anything...to make a correction, you debit and then credit (keeping the sum happy) and yet you have the audit trail...very cool...I am liking this stuff... And finally you mentioned that bank accounts are tricky...can you expand on this please. After all I am under the impression that "bank accounts" are a corner stone of this whole book keeping...I mean...bank accounts have debits and credits just like any account...eg interest earned is a credit and bank fees are debits...what worries you about bank accounts... Thanks guys...this is very very nice Medi On Feb 6, 2008 6:35 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > At 05:09 PM 2/6/2008, [EMAIL PROTECTED] wrote: > >Date: Wed, 6 Feb 2008 17:08:54 -0800 > >From: "Medi Montaseri" <[EMAIL PROTECTED]> > >To: pgsql-sql@postgresql.org > >Subject: accounting schema > >Message-ID: > ><[EMAIL PROTECTED]> > > > >Hi, > > > >I am learning my way into Accounting and was wondering how Accounting > >applications are designed. perhaps you could point the way > > > >On one hand, accountants talk about a sacret equation A = L + OE > >(Asset = > >Libility + Owner Equity) and then under each categories there are one > >or > >many account. On the other hand a DBA thinks in terms of tables and > >relations. Instead of getting theoritical, allow me to setup an > >example > > Hi Medi, > > You might read some source code and docs for open source accounting > software and see "how it's done." Here's one example that might be > interesting and simple enough to follow: > > http://www.gnucash.org/ > > In general, I think there are many different accounting methods, so you > have to get clear about which one you're using. "Double-entry" > accounting is common. Cash vs. accrual posting methods matter (i.e. > when does an expense or receivable "charge" against the assets > balance?) > > My most basic understanding is that in general you track assets as they > come in, to an "Accounts Receivable" ledger (i.e. table) and > liabilities to an "Accounts Payable" ledger. Then you reconcile these > two "books" into a "General Ledger" table which gives you something > like an audit trail of all activity (and a running balance). I'm sure > Wikipedia will define these three terms and lots more with greater > clarity. > > But my (limited) experience with accounting schema is that they often > involve these three tables (AR/AP/GL) at their core. > > As you add bank accounts, complex investment instruments, depreciation > etc, things get considerably more complex of course. > > I'll readily admit my limited experience, and I'm sure others on this > list have far better information. I hope this gets you started anyway. > > Sincerely, > > Steve > >
Re: [SQL] accounting schema
Medi Montaseri wrote: Hi, I am learning my way into Accounting and was wondering how Accounting applications are designed. perhaps you could point the way On one hand, accountants talk about a sacret equation A = L + OE (Asset = Libility + Owner Equity) and then under each categories there are one or many account. On the other hand a DBA thinks in terms of tables and relations. Instead of getting theoritical, allow me to setup an example Say you have have construction project (like a room addition) or one of those flip this house deals Owner brings the land (equity) of say worth $100K Expenses begin to mount ( that is a minus against OE) Account Payble begins to mount (that is a liability) And one day you experience a sale As a DBA, (and keeping it simple) I am thinking I need a table for every account which migh look like id, description, credit, debit, validated, created_on, created_by, modified_on, modified_by Is that pretty match it ? Please let me know if you have seen some accounting or DB book that addresses this problem domain. Thanks Medi My home-grown system uses three key tables: account, transaction and split. The split joins a transaction to an account and an amount. All the splits for a single transaction must sum to zero (checked by a PL/pgSQL function triggered on insert, update and delete on the split table). For example, my pay is a single transaction with typically 8 splits reflecting what my pay-master does with my pay (tax, superannuation, health contrib, etc.) I also have other tables for managing reconciliations - each split has a reconciliation ID that indicates if/when that split was reconciled. Once reconciled, the split becomes, effectively, immutable (by the same PL/pgSQL function). Transactions contain date, description, who etc. (all from the top of my head - I should check what I really did many years ago). Most of the rest of it is then just mapping the accounts to the various ledgers and bank accounts etc. This model is very simple, for very simple people like me. Cheers, Bob Edwards. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] accounting schema
At 06:54 PM 2/6/2008, Medi Montaseri wrote: Thanks Steve... And finally you mentioned that bank accounts are tricky...can you expand on this please. After all I am under the impression that "bank accounts" are a corner stone of this whole book keeping...I mean...bank accounts have debits and credits just like any account...eg interest earned is a credit and bank fees are debits...what worries you about bank accounts... Thanks guys...this is very very nice Medi Hi Medi, We may be well off-topic for Pgsql but it is a modeling issue so I'll reply on-list.. Consider this scenario: You want to track your AP/AR accounts by "business unit" - so you have a Marketing account, Sales account, Engineering account, etc. But let's say you want to keep all the money for all the "units" in only two bank accounts depending on which region the expenses will be paid out from (to reduce processing fees). So you might pay for things accrued by Engineering and Marketing on the West Coast from one bank account and Engineering and Marketing expenses on the East Coast from the other (a US-centric example). So the bank accounts where cash is actually deposited/withdrawn is different from where the money is received or spent from a "logical" perspective in the AR/AP ledgers. This is a simple example and real-world issues become truly horrendous. (For example, try to account for investment instruments like long term property holdings with lines of credit against the real estate). I've always thought that if DBA's existed when Accounting was invented, things would look very, very different. :) Good luck, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] TG_TABLE_NAME as identifier
Thanks for the precious tips, but unfortunately the syntax: EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT '|| NEW.*;is parsed correctly, but when the trigger is fired the following error is returned ERROR: column "*" not found in data type mytable CONTEXT: SQL statement "SELECT 'INSERT INTO '|| $1 ||' SELECT '|| $2 .*" Using EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.*'; instead, returns: ERROR: NEW used in query that is not in a rule CONTEXT: SQL statement "INSERT INTO anag_referenti SELECT NEW.*" Hope someone can show me another way to do that, if exists ;) Have a nice day! T. _ Ti piace giocare con le lettere? Prova ABCLive! http://messengergiochi.it.msn.com/
Re: [SQL] TG_TABLE_NAME as identifier
Tiziano Slack wrote: Thanks for the precious tips, but unfortunately the syntax: EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT '|| NEW.*;is parsed correctly, but when the trigger is fired the following error is returned ERROR: column "*" not found in data type mytable CONTEXT: SQL statement "SELECT 'INSERT INTO '|| $1 ||' SELECT '|| $2 .*" Using EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.*'; instead, returns: ERROR: NEW used in query that is not in a rule CONTEXT: SQL statement "INSERT INTO anag_referenti SELECT NEW.*" Hope someone can show me another way to do that, if exists ;) You'll have to build the INSERT in full - column by column. Have you considered plperl / pltcl etc. - that makes it easier to do. -- Richard Huxton Archonet Ltd ---(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
