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

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