what's the scope of psql parameter values?

"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

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