Thread: Postgres plpgsql

Postgres plpgsql

From

Shaun Clements

Date:

30 March 2005, 17:45:34

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.

Kind Regards,
Shaun Clements

Re: Postgres plpgsql

From

Richard Huxton

Date:

30 March 2005, 17:56:06

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

Re: Postgres plpgsql

From

Shaun Clements

Date:

30 March 2005, 18:37:47

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

Re: Postgres plpgsql

From

John DeSoi

Date:

30 March 2005, 18:59:55

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