Postgres Pro
Downloads
Home   >   mailing lists

Thread: what's the scope of psql parameter values?

what's the scope of psql parameter values?

From
"Dick Wieland"
Date:

Hi,

 

I'm finding that the following function does not run properly ...

 

CREATE OR REPLACE FUNCTION :S.insert_scripts ( text )

RETURNS integer AS '

DECLARE

  my_script_type alias for $1;

  my_script_type_id integer;

BEGIN

    my_script_type_id := 0;

    select into   my_script_type_id   type_id     from :S.scripts_ref where

    type_name = my_script_type ;

    return my_script_type_id;

END;

' LANGUAGE 'plpgsql' ;

 

it fails on the ":S" parameter substitution in line 6.

 

wiline=# \echo :S

rwieland

wiline=# select :S.isf('update');

WARNING:  Error occurred while executing PL/pgSQL function isf

WARNING:  line 6 at select into variables

ERROR:  parser: parse error at or near ":" at character 22

 

Is this expected behavior I wonder?  Does the parameter substitution facility not extend down "into" the function space ...

 

Thanks for any comments,

 

Dick Wieland

 

---

 

Re: what's the scope of psql parameter values?

From
Doug McNaught
Date:
"Dick Wieland" <dick.wieland@wiline.com> writes:

> wiline=# select :S.isf('update');
> WARNING:  Error occurred while executing PL/pgSQL function isf
> WARNING:  line 6 at select into variables
> ERROR:  parser: parse error at or near ":" at character 22
>
> Is this expected behavior I wonder?  Does the parameter substitution
> facility not extend down "into" the function space ...

The function body is just a single-quoted SQL string as far as psql is
concerned, and it doesn't do variable substitution inside quoted
strings.

-Doug

Re: what's the scope of psql parameter values?

From
"Nigel J. Andrews"
Date:
On Thu, 17 Jul 2003, Dick Wieland wrote:

> Hi,
>
> I'm finding that the following function does not run properly ...
>
> CREATE OR REPLACE FUNCTION :S.insert_scripts ( text )
> RETURNS integer AS '
> DECLARE
>   my_script_type alias for $1;
>   my_script_type_id integer;
> BEGIN
>     my_script_type_id := 0;
>     select into   my_script_type_id   type_id     from :S.scripts_ref
> where
>     type_name = my_script_type ;
>     return my_script_type_id;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> it fails on the ":S" parameter substitution in line 6.
>
> wiline=# \echo :S
> rwieland
> wiline=# select :S.isf('update');
> WARNING:  Error occurred while executing PL/pgSQL function isf
> WARNING:  line 6 at select into variables
> ERROR:  parser: parse error at or near ":" at character 22
>
> Is this expected behavior I wonder?  Does the parameter substitution
> facility not extend down "into" the function space ...
>
> Thanks for any comments,
>
> Dick Wieland
>

Believe me I know _exactly_ what you mean and you wouldn't believe the time I
put into trying to get the psql variable to expand so it could be used in a
function body. In the end I gave up and just added another s/// to the sed my
script with it in.

As someone else has already said, psql variables don't expand in a single
quoted string. Afterall how does psql know ':something' isn't supposed to be
':something' and not the a quote value of a variable?


--
Nigel J. Andrews