Postgres Pro
Downloads
Home   >   mailing lists

Thread: About functions

About functions

From
Devrim GUNDUZ
Date:
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?

Regards and best wishes,


Devrim GUNDUZ

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

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

Re: About functions

From
Stephan Szabo
Date:
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).

Re: About functions

From
Devrim GUNDUZ
Date:
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
------------------------------------------------------------------

Re: About functions

From
"Gregory Wood"
Date:
> > 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

Re: About functions

From
Stephan Szabo
Date:
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.