Skip site navigation (1) Skip section navigation (2)

Re: allowed variable names in functions?

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "A B *EXTERN*" <gentosaker(at)gmail(dot)com>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: allowed variable names in functions?
Date: 2008-06-30 13:48:32
Message-ID: D960CB61B694CF459DCFB4B0128514C20244E034@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-general
A B wrote:
> Here is the entire function and it fails with the names
> c2,c2div,c3,c3div, but if names are changed, it works!
> (by works I mean I get the "hello" lines printed) There is nothing
> wrong with the select statement either, that works fine if I run it
> stand-alone, or with the names of c2,c2div,c3,c3div changed.
> 
> CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
> DECLARE
> 	c2 REAL;
> 	c2div REAL;
> 	c3 REAL;
> 	c3div REAL;
> 	weights RECORD;
> 	tmp RECORD;
> 	retval RECORD;
> 	t RECORD;
> BEGIN
> RAISE NOTICE 'starting...';
> 	FOR tmp IN SELECT id,c2,c3 FROM Master  WHERE pid=pid_  AND c3 !=0 LOOP
> 		RAISE NOTICE 'hello %',tmp.id;
> 	END LOOP;
> 	RETURN;
> END; $$  LANGUAGE plpgsql;

That is because c2 and c3 in the SELECT statement are replaced with
the variables before the SQL statement is executed.

See the documentation:
http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

You should qualify the column names:

SELECT master.id, master.c2, master.c3 FROM master WHERE ... AND master.c3 <> 0

Yours,
Laurenz Albe

In response to

pgsql-general by date

Next:From: Henry - Zen Search SADate: 2008-06-30 13:59:58
Subject: Re: "out of balance" result on select from suspected index corruption [RESOLVED]
Previous:From: Sam MasonDate: 2008-06-30 12:51:31
Subject: Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group