Thread: Text Variable in a Function
Hi,
I am trying to write a function, and have written this :
var_params_shootingstar :=
$v$
WHERE
(
t1.status = TRUE,
AND (
(
(
SELECT
z( pointn( geometryn(d3.the_geom,1), 1) ) AS z
FROM streets_3d d3
WHERE d3.gid = t1.gid
) / $v$ || var_rain_cm || $v$
) >= $v$ || var_ratio_car_rain || $v$
),
AND (
$v$ || var_ratio_cartype_rain || $v$ <= 10
)
)
$v$;
RAISE NOTICE 'Where clause : %', quote_literal(var_params_shootingstar);
Instead of $v$ I also tried $vps$ and ‘ . I also tried removing quote_literal() to no avail.
My problem is that I get : NOTICE: Where clause : <NULL> when I try to execute the function.
I tried changing it to var_params_shootingstar := $v$ this is a test $v$ || var_rain_cm || $v$ end of test $v$;
And this is the result I got : NOTICE: Where clause : this is a test 0 end of test
BTW : var_params_shootingstar is declared as TEXT.
I know this is a very strange problem, hence why I am asking here, been trying different things for more than an hour.
Any help is extremely appreciated.
Thanks and regards
Matthew
I am using the free version of SPAMfighter for private users.
It has removed 26496 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!
On Wednesday 09 April 2008 06:46, Matthew Pulis wrote: > Hi, > > > > I am trying to write a function, and have written this : > > > > var_params_shootingstar := > > $v$ > > WHERE > > ( > > t1.status = TRUE, > > AND ( > > ( > > ( > > SELECT > > z( pointn( > geometryn(d3.the_geom,1), 1) ) AS z > > FROM streets_3d d3 > > WHERE d3.gid = t1.gid > > ) / $v$ || var_rain_cm || $v$ > > ) >= $v$ || var_ratio_car_rain || $v$ > > ), > > AND ( > > $v$ || var_ratio_cartype_rain || $v$ <= > 10 > > ) > > ) > > $v$; > > > > > > RAISE NOTICE 'Where clause : %', > quote_literal(var_params_shootingstar); > > > > Instead of $v$ I also tried $vps$ and ' . I also tried removing > quote_literal() to no avail. > > > > My problem is that I get : NOTICE: Where clause : <NULL> when I try to > execute the function. > > > > I tried changing it to var_params_shootingstar := $v$ this is a test $v$ > > || var_rain_cm || $v$ end of test $v$; > > And this is the result I got : NOTICE: Where clause : this is a test 0 > end of test > > > > BTW : var_params_shootingstar is declared as TEXT. > > > > I know this is a very strange problem, hence why I am asking here, been > trying different things for more than an hour. > > > > Any help is extremely appreciated. > > > > Thanks and regards > > > > Matthew > > > > _____ > > I am using the free version of SPAMfighter for private users. > It has removed 26496 spam emails to date. > Paying users do not have this message in their emails. > Try SPAMfighter <http://www.spamfighter.com/len> for free now! If one of your variables is null, then the whole expression will be null. This may be your problem. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
Thanks a lot! That did the trick :) Was trying to fix the TEXT query and forgetting about the variables (it was one of the variables -had a missing _ ) ehh :) Thanks again! -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Terry Lee Tucker Sent: Wednesday, April 09, 2008 12:59 PM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Text Variable in a Function On Wednesday 09 April 2008 06:46, Matthew Pulis wrote: > Hi, > > > > I am trying to write a function, and have written this : > > > > var_params_shootingstar := > > $v$ > > WHERE > > ( > > t1.status = TRUE, > > AND ( > > ( > > ( > > SELECT > > z( pointn( > geometryn(d3.the_geom,1), 1) ) AS z > > FROM streets_3d d3 > > WHERE d3.gid = t1.gid > > ) / $v$ || var_rain_cm || $v$ > > ) >= $v$ || var_ratio_car_rain || $v$ > > ), > > AND ( > > $v$ || var_ratio_cartype_rain || $v$ <= > 10 > > ) > > ) > > $v$; > > > > > > RAISE NOTICE 'Where clause : %', > quote_literal(var_params_shootingstar); > > > > Instead of $v$ I also tried $vps$ and ' . I also tried removing > quote_literal() to no avail. > > > > My problem is that I get : NOTICE: Where clause : <NULL> when I try to > execute the function. > > > > I tried changing it to var_params_shootingstar := $v$ this is a test $v$ > > || var_rain_cm || $v$ end of test $v$; > > And this is the result I got : NOTICE: Where clause : this is a test 0 > end of test > > > > BTW : var_params_shootingstar is declared as TEXT. > > > > I know this is a very strange problem, hence why I am asking here, been > trying different things for more than an hour. > > > > Any help is extremely appreciated. > > > > Thanks and regards > > > > Matthew > > > > _____ > > I am using the free version of SPAMfighter for private users. > It has removed 26496 spam emails to date. > Paying users do not have this message in their emails. > Try SPAMfighter <http://www.spamfighter.com/len> for free now! If one of your variables is null, then the whole expression will be null. This may be your problem. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice -- I am using the free version of SPAMfighter for private users. It has removed 26522 spam emails to date. Paying users do not have this message in their emails. Get the free SPAMfighter here: http://www.spamfighter.com/len