Postgres plpgsql

Shaun Clements wrote:

Im finding a problem with updating in PGPLSQL
Can anyone find anything wrong with this code below. It appears Postgres
isnt picking up a change in the dynamic first column. So the same column is
being updated.
.
<snip>
EXECUTE ''UPDATE TABLENAME SET
''||quote_ident(variable1)||''=''||quote_literal(Variable2)||'' where
columnname1 =''||quote_literal(variable3)||'' and columnname2 =
''||quote_literal(variable4)||'' and month = ''''07'''' and year =
''''2004'''''';
</snip>

There is clearly a need for a debugging facility.

You're not wrong there. However, you can use RAISE NOTICE - something like
RAISE NOTICE ''my vars = % / %'',variable1,variable2;

--
Richard Huxton
Archonet Ltd

Hi Richard

Thanks for your help.
Im stuck on this statement.
Am I missing the '' ???
<snip>
PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = ''''||
quote_literal(VARIABLE1)'' ||'' AND COLUMN2 =
''''||quote_literal(VARIABLE2)||'''';
IF NOT FOUND THEN
</snip>

The statement is failing to perform the check in the function.

Kind Regards,
Shaun Clements
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 30 March 2005 03:56 PM
To: Shaun Clements
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres plpgsql

Shaun Clements wrote:

Im finding a problem with updating in PGPLSQL
Can anyone find anything wrong with this code below. It appears Postgres
isnt picking up a change in the dynamic first column. So the same column

is

being updated.
.
<snip>
EXECUTE ''UPDATE TABLENAME SET
''||quote_ident(variable1)||''=''||quote_literal(Variable2)||'' where
columnname1 =''||quote_literal(variable3)||'' and columnname2 =
''||quote_literal(variable4)||'' and month = ''''07'''' and year =
''''2004'''''';
</snip>

There is clearly a need for a debugging facility.

You're not wrong there. However, you can use RAISE NOTICE - something like
RAISE NOTICE ''my vars = % / %'',variable1,variable2;

--
Richard Huxton
Archonet Ltd

On Mar 30, 2005, at 9:34 AM, Shaun Clements wrote:

PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = ''''||
quote_literal(VARIABLE1)'' ||'' AND COLUMN2 =
''''||quote_literal(VARIABLE2)||'''';

The first one you posted looked correct. This line above seems to have
problems. I think you want this:

PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = '''' ||
quote_literal(VARIABLE1) '''' || '' AND COLUMN2 = '' || '''' ||
quote_literal(VARIABLE2) || '''';

Generally PERFORM is used for side effects only. I don't see why you
would use PERFORM unless this line will change the database somehow.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL