[SQL] Help needed
Hi everyone, I have some kind of strange problem. I want to build general parametrized trigger for my database. The parameter sent to the trigger contains a column name that trigger should be check. CREATE OR REPLACE FUNCTION F_T_IU__check() RETURNS trigger AS $$ DECLARE vt VARCHAR; BEGIN ... vt:='constant_value_id'; RAISE EXCEPTION 'aaa=%', NEW.vt; . The 'vt' variable contains the column name. In the above statement I want that NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this? Thanks, Nosy ---(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 needed
NosyMan wrote: Hi everyone, I have some kind of strange problem. I want to build general parametrized trigger for my database. The parameter sent to the trigger contains a column name that trigger should be check. The 'vt' variable contains the column name. In the above statement I want that NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this? Not in pl/pgsql - try one of the more interpreted languages - tcl/perl/python etc. Oh, and try not to reply to a previous message when starting a new thread - it can mess with threading in some mail packages. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Archives site down?
Apologies if this has been mentioned on announce (I'm not subscribed), but the mailing list archive site appears to have a problem (403 Forbidden): http://archives.postgresql.org/ ---(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] Archives site down?
El Mié 30 Nov 2005 09:57, Neil Saunders escribió: > Apologies if this has been mentioned on announce (I'm not subscribed), > but the mailing list archive site appears to have a problem (403 > Forbidden): > > http://archives.postgresql.org/ I'm checking the archives right now, without a problem. -- 10:16:01 up 20 days, 23:05, 1 user, load average: 0.47, 0.69, 0.83 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] problems with array
On Sat, 15 Oct 2005 08:49:15 + "paperinik 100" <[EMAIL PROTECTED]> threw this fish to the penguins: > PostgreSQL is 7.4.7. > > My first table > CREATE TABLE tb_cat ( > id INTEGER, > desc text > ); > INSERT INTO tb_cat VALUES (10, 'cat10'); > INSERT INTO tb_cat VALUES (20, 'cat20'); > INSERT INTO tb_cat VALUES (30, 'cat30'); > > My second table > CREATE TABLE tb_array( > id INTEGER, > cat INTEGER[] > ); > INSERT INTO tb_array VALUES(1, ARRAY [10, 20]); > > When I write my select > SELECT * from tb_cat WHERE id IN (SELECT cat FROM tb_array WHERE id=1); > the output is: > ERROR: operator does not exist: integer = integer[] > HINT: No operator matches the given name and argument type(s). You may need > to add explicit type casts. Use the "any" function (pseudo function? builtin? whatever); no subquery is needed: select c.* from tb_cat c,tb_array a where a.id=1 and c.id=any(a.cat); Look at section 8.10.5 "Searching in Arrays" in http://www.postgresql.org/docs/7.4/interactive/arrays.html and section 9.17.3 in: http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] child fk problem
I there, I'm trying to work with postgre, but i'm having a problem with inherits. I have a table (parent) that as an fk to another table. When i create a child, i loose the connection to the other table. i dont need to insert values in the parent table. what do i need to do?? tks in advance
[SQL] quote_literal() fails with unmatched quotes
I have had the following problem and been unable to determine the cause !
nmostlikemat := iso_date(nmostlikemat);
EXECUTE ''insert into scenario_inv (''
|| ''inv_id,''
|| ''record_date,''
|| ''scenario_type,''
|| ''mostlikemat,''
|| ''mktpr,''
|| ''mktyld )''
|| '' values ( ''
|| quote_literal(ID)
|| '',''
|| quote_literal(nnrecord_date)
|| '',''
|| quote_literal(nscid)
|| '',''
|| quote_literal(nmostlikemat)
|| '',''
|| quote_literal(nmktpr)
|| '',''
|| quote_literal(nmktyld)
|| '');'';
The following error returns from postgres 8.1
ERROR: unterminated quoted string at or near "'2009-10-03" at character 122
QUERY: insert into scenario_inv
(inv_id,record_date,scenario_type,mostlikemat,mktpr,mktyld ) values (
'405832','2005-06-30','1','2009-10-03
CONTEXT: PL/pgSQL function "scenario_set" line 502 at execute statement
PL/pgSQL function "fix_invest_set_scenario" line 18 at if
LINE 1: ...mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-0...
James M Doherty
Principal
JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] quote_literal() fails with unmatched quotes
"James M Doherty" <[EMAIL PROTECTED]> writes: > I have had the following problem and been unable to determine the cause ! Can you show us a complete example? I can't see what's going on from the info you've provided ... 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] Quote_literal()
declare SQL text; NSQLtext; SQL := ''10/3/2009''; NSQL := quote_literal(SQL); The result is '10/3/2009 with no closing quote. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] quote_literal() fails with unmatched quotes
Nmostlikemat := ''10/3/2009''; What happens is that quote_literal works for everyone in the example but this one. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James M Doherty Sent: Wednesday, November 30, 2005 2:33 PM To: [email protected] Subject: [SQL] quote_literal() fails with unmatched quotes I have had the following problem and been unable to determine the cause ! nmostlikemat := iso_date(nmostlikemat); EXECUTE ''insert into scenario_inv ('' || ''inv_id,'' || ''record_date,'' || ''scenario_type,'' || ''mostlikemat,'' || ''mktpr,'' || ''mktyld )'' || '' values ( '' || quote_literal(ID) || '','' || quote_literal(nnrecord_date) || '','' || quote_literal(nscid) || '','' || quote_literal(nmostlikemat) || '','' || quote_literal(nmktpr) || '','' || quote_literal(nmktyld) || '');''; The following error returns from postgres 8.1 ERROR: unterminated quoted string at or near "'2009-10-03" at character 122 QUERY: insert into scenario_inv (inv_id,record_date,scenario_type,mostlikemat,mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-03 CONTEXT: PL/pgSQL function "scenario_set" line 502 at execute statement PL/pgSQL function "fix_invest_set_scenario" line 18 at if LINE 1: ...mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-0... James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] child fk problem
On Wed, 2005-11-30 at 12:42, Luis Silva wrote: > I there, I'm trying to work with postgre, but i'm having a problem > with inherits. I have a table (parent) that as an fk to another table. > When i create a child, i loose the connection to the other table. i > dont need to insert values in the parent table. what do i need to do?? > tks in advance Neither indexes, nor triggers, nor foreign keys are inherited by child tables. only the base structure of the parent. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Quote_literal()
"James M Doherty" <[EMAIL PROTECTED]> writes: > declare > SQL text; > NSQLtext; > SQL := ''10/3/2009''; > NSQL := quote_literal(SQL); > The result is '10/3/2009 with no closing quote. Works for me: regression=# create function foo() returns text as ' regression'# declare SQL text; regression'# NSQLtext; regression'# begin regression'# SQL := ''10/3/2009''; regression'# NSQL := quote_literal(SQL); regression'# return NSQL; regression'# end' language plpgsql; CREATE FUNCTION regression=# select foo(); foo - '10/3/2009' (1 row) Again, I suggest giving a *complete* example, because you are obviously not looking in the right place for your problem. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
