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