About functions

On Sun, 24 Feb 2002, Devrim GUNDUZ wrote:

Hi,

While working on functions, I had a problem:

I wanted to write a function whict would drop my function. It is simply as
below:

CREATE FUNCTION dropfunc(text) RETURNS integer AS '
DROP FUNCTION $1 ;
SELECT 1;
'
LANGUAGE SQL;

But PostgreSQL answers:

test=# ERROR: parser: parse error at or near "$1"

What is the error in here?

You can't use the argument as a parameter directly in that sql statement.
You might be able to do:
EXECUTE ''DROP FUNCTION '' || $1;

(Note also that the above requires arguments of the form
foo(int) not just foo).

Hi,

On Sun, 24 Feb 2002, Stephan Szabo wrote:

You can't use the argument as a parameter directly in that sql statement.
You might be able to do:
EXECUTE ''DROP FUNCTION '' || $1;

Thanks but, again an error:

test=# CREATE FUNCTION dropfunc(text) RETURNS integer AS '
test'# BEGIN;
test'# EXECUTE ''DROP FUNCTION'' $1;
test'# SELECT 1;
test'# END;
test'# '
test-# LANGUAGE SQL;
ERROR: parser: parse error at or near "EXECUTE"
test=#

Let me dive into my manuals :)

Regards

--

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
devrimg@tr.net

Web : http://devrim.oper.metu.edu.tr
------------------------------------------------------------------

You can't use the argument as a parameter directly in that sql

statement.

You might be able to do:
EXECUTE ''DROP FUNCTION '' || $1;

Thanks but, again an error:

test=# CREATE FUNCTION dropfunc(text) RETURNS integer AS '
test'# BEGIN;
test'# EXECUTE ''DROP FUNCTION'' $1;
test'# SELECT 1;
test'# END;
test'# '
test-# LANGUAGE SQL;
ERROR: parser: parse error at or near "EXECUTE"
test=#

Let me dive into my manuals :)

I'm pretty sure EXECUTE is only available in the PL/pgSQL language, and not
the SQL language. Also, you need to append the parameter to the rest of the
drop function string, so be sure to include a space after FUNCTION and
concat the two together with ||.

Greg

On Mon, 25 Feb 2002, Gregory Wood wrote:

You can't use the argument as a parameter directly in that sql

statement.

You might be able to do:
EXECUTE ''DROP FUNCTION '' || $1;

Thanks but, again an error:

test=# CREATE FUNCTION dropfunc(text) RETURNS integer AS '
test'# BEGIN;
test'# EXECUTE ''DROP FUNCTION'' $1;
test'# SELECT 1;
test'# END;
test'# '
test-# LANGUAGE SQL;
ERROR: parser: parse error at or near "EXECUTE"
test=#

Let me dive into my manuals :)

I'm pretty sure EXECUTE is only available in the PL/pgSQL language, and not
the SQL language. Also, you need to append the parameter to the rest of the
drop function string, so be sure to include a space after FUNCTION and
concat the two together with ||.

And, I think the begin/end are illegal in sql language functions
(since the last statement isn't a select) and there shouldn't be a
semicolon after begin for plpgsql.