Thread: Text Variable in a Function

Text Variable in a Function

From
"Matthew Pulis"
Date:

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!

Re: Text Variable in a Function

From
Terry Lee Tucker
Date:
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

Re: Text Variable in a Function

From
"Matthew Pulis"
Date:
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