Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to drop function?

how to drop function?

From
"J.V."
Date:
How do I drop a function that was created like so:

create or replace function process_table (action TEXT, v_table_name
varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
....
BEGIN
   ...
END;
$$ LANGUAGE plpgsql;

---
I have tried various ways, but it always fails.


J.V.

Re: how to drop function?

From
Craig Ringer
Date:
On 11/16/2011 07:38 AM, J.V. wrote:
> How do I drop a function that was created like so:
>
> create or replace function process_table (action TEXT, v_table_name
> varchar(100)) RETURNS BOOLEAN
> AS $$
> DECLARE
> ....
> BEGIN
> ...
> END;
> $$ LANGUAGE plpgsql;
>
> ---
> I have tried various ways, but it always fails.

DROP FUNCTION process_table (action TEXT, v_table_name varchar(100));

--
Craig Ringer

Re: how to drop function?

From
Rebecca Clarke
Date:
DROP FUNCTION process_table;

should work.


On Tue, Nov 15, 2011 at 11:38 PM, J.V. <jvsrvcs@gmail.com> wrote:
How do I drop a function that was created like so:

create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
....
BEGIN
 ...
END;
$$ LANGUAGE plpgsql;

---
I have tried various ways, but it always fails.


J.V.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: how to drop function?

From
Adrian Klaver
Date:
On Tuesday, November 15, 2011 3:56:32 pm Rebecca Clarke wrote:
> DROP FUNCTION process_table;
>
> should work.
>

Actually no, for the following reason:(

http://www.postgresql.org/docs/9.0/interactive/sql-dropfunction.html
"DROP FUNCTION removes the definition of an existing function. To execute this
command the user must be the owner of the function. The argument types to the
function must be specified, since several different functions can exist with the
same name and different argument lists"

--
Adrian Klaver
adrian.klaver@gmail.com

Re: how to drop function?

From
Ivan Sergio Borgonovo
Date:
On Tue, 15 Nov 2011 16:38:20 -0700
"J.V." <jvsrvcs@gmail.com> wrote:

> How do I drop a function that was created like so:
>
> create or replace function process_table (action TEXT,
> v_table_name varchar(100)) RETURNS BOOLEAN
> AS $$
> DECLARE
> ....
> BEGIN
>    ...
> END;
> $$ LANGUAGE plpgsql;
>
> ---
> I have tried various ways, but it always fails.
>
>
> J.V.
>

test=# begin;
create or replace function process_table (
        action TEXT, v_table_name varchar(100)
) RETURNS BOOLEAN
AS $$
DECLARE

BEGIN
        return true;
END;
$$ LANGUAGE plpgsql;

drop function process_table (
        action TEXT, v_table_name varchar(100)
);
commit;
BEGIN
CREATE FUNCTION
DROP FUNCTION
COMMIT
test=#

Repeat just the input parameters.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Re: how to drop function?

From
"J.V."
Date:
this did not work.

On 11/15/2011 4:56 PM, Craig Ringer wrote:
> On 11/16/2011 07:38 AM, J.V. wrote:
>> How do I drop a function that was created like so:
>>
>> create or replace function process_table (action TEXT, v_table_name
>> varchar(100)) RETURNS BOOLEAN
>> AS $$
>> DECLARE
>> ....
>> BEGIN
>> ...
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> ---
>> I have tried various ways, but it always fails.
>
> DROP FUNCTION process_table (action TEXT, v_table_name varchar(100));
>
> --
> Craig Ringer
>

Re: how to drop function?

From
"J.V."
Date:
this does not work.

On 11/15/2011 4:56 PM, Rebecca Clarke wrote:
DROP FUNCTION process_table;

should work.


On Tue, Nov 15, 2011 at 11:38 PM, J.V. <jvsrvcs@gmail.com> wrote:
How do I drop a function that was created like so:

create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
....
BEGIN
 ...
END;
$$ LANGUAGE plpgsql;

---
I have tried various ways, but it always fails.


J.V.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: how to drop function?

From
"J.V."
Date:
the drop function works when running from a pgAdmin III Sql window

but when I try to do from the command line and script it:
    psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c "drop function ...."

the above fails.

It does however work with functions with no params or a single param.  It seems to get hung up on the comma and the extra set of parenthesis


On 11/15/2011 5:01 PM, Ivan Sergio Borgonovo wrote: 
On Tue, 15 Nov 2011 16:38:20 -0700
"J.V." <jvsrvcs@gmail.com> wrote:


How do I drop a function that was created like so:

create or replace function process_table (action TEXT,
v_table_name varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
....
BEGIN  ...
END;
$$ LANGUAGE plpgsql;

---
I have tried various ways, but it always fails.


J.V.


test=# begin;                  
create or replace function process_table (       action TEXT, v_table_name varchar(100)
) RETURNS BOOLEAN
AS $$
DECLARE

BEGIN       return true;
END;
$$ LANGUAGE plpgsql;

drop function process_table (       action TEXT, v_table_name varchar(100)
);
commit;
BEGIN
CREATE FUNCTION
DROP FUNCTION
COMMIT
test=#

Repeat just the input parameters.

Re: how to drop function?

From
David Johnston
Date:
On Nov 15, 2011, at 20:24, "J.V." <jvsrvcs@gmail.com> wrote:

this did not work.

On 11/15/2011 4:56 PM, Craig Ringer wrote:
On 11/16/2011 07:38 AM, J.V. wrote:
How do I drop a function that was created like so:

create or replace function process_table (action TEXT, v_table_name
varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
....
BEGIN
...
END;
$$ LANGUAGE plpgsql;

---
I have tried various ways, but it always fails.

DROP FUNCTION process_table (action TEXT, v_table_name varchar(100));

--
Craig Ringer


If you are going to claim something doesn't work it really helps to provide the clues that lead you to that conclusion.  Specifically, what error message(s) are you seeing?

The parameter names and the (100) are both optional so try removing them and see what happens.

David J.

Re: how to drop function?

From
Scott Marlowe
Date:
On Tue, Nov 15, 2011 at 6:48 PM, J.V. <jvsrvcs@gmail.com> wrote:
> the drop function works when running from a pgAdmin III Sql window
>
> but when I try to do from the command line and script it:
>     psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c "drop function
> ...."
>
> the above fails.


What's the rest of that line look like?  What error do you get?

Re: how to drop function?

From
John R Pierce
Date:
On 11/15/11 5:48 PM, J.V. wrote:
> the drop function works when running from a pgAdmin III Sql window
>
> but when I try to do from the command line and script it:
>     psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c "*drop
> function ....*"
>
> the above fails.

can you please give the complete command line and the error message you
get instead of just saying 'fails' ?

btw, if in fact PGHOST, PGPORT PGDATABASE and PGUSER are set in the
environment, you don't need to specify any of those on the command
line.  but if those are just placeholders for actual names, well, we
can't tell that from here.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Re: how to drop function?

From
Ivan Sergio Borgonovo
Date:
On Tue, 15 Nov 2011 18:48:00 -0700
"J.V." <jvsrvcs@gmail.com> wrote:

> the drop function works when running from a pgAdmin III Sql window
>
> but when I try to do from the command line and script it:
>      psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c
> "*drop function ....*"
>
> the above fails.

> It does however work with functions with no params or a single
> param. It seems to get hung up on the comma and the extra set of
> parenthesis

It would be nice to know how it fails and if you reposted exactly
what you wrote to make it fail and what you wrote to make it succede.

What does it mean "get hung on the comma and extra set of
parenthesis"?

ivan@dawn:~$ psql -h lan test -c 'drop function process_table
  (action TEXT, v_table_name varchar(100));'
DROP FUNCTION
ivan@dawn:~$

the psql command all on the same line.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Re: how to drop function?

From
Thomas Kellerer
Date:
Ivan Sergio Borgonovo, 16.11.2011 01:01:
> test=# begin;
> create or replace function process_table (
>          action TEXT, v_table_name varchar(100)
> ) RETURNS BOOLEAN
> AS $$
> DECLARE
>
> BEGIN
>          return true;
> END;
> $$ LANGUAGE plpgsql;
>
> drop function process_table (
>          action TEXT, v_table_name varchar(100)
> );
> commit;
> BEGIN
> CREATE FUNCTION
> DROP FUNCTION
> COMMIT
> test=#
>
> Repeat just the input parameters.

You don't have to include the parameter names though

    drop function process_table (TEXT, varchar(100));

is just as good and bit less typing ;)

Re: how to drop function?

From
Ivan Sergio Borgonovo
Date:
On Wed, 16 Nov 2011 09:17:45 +0100
Thomas Kellerer <spam_eater@gmx.net> wrote:

> Ivan Sergio Borgonovo, 16.11.2011 01:01:
> > test=# begin;
> > create or replace function process_table (
> >          action TEXT, v_table_name varchar(100)
> > ) RETURNS BOOLEAN
> > AS $$
> > DECLARE
> >
> > BEGIN
> >          return true;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > drop function process_table (
> >          action TEXT, v_table_name varchar(100)
> > );
> > commit;
> > BEGIN
> > CREATE FUNCTION
> > DROP FUNCTION
> > COMMIT
> > test=#
> >
> > Repeat just the input parameters.
>
> You don't have to include the parameter names though
>
>     drop function process_table (TEXT, varchar(100));

> is just as good and bit less typing ;)

In psql/pgadmin you've tab completion. It will complete without the
parameters name.
If you're writing more durable code generally you can just cut&paste
the creation code.

I admit I haven't spent enough time to see if I can have tab
completion inside my IDE/editor.

When you're refactoring the function most probably you'll have to
refactor the drop code too.

I tend to refactor much more frequently the number/type of parameters
rather than the names, so skipping the names is anticipating some
work that I'll seldom take advantage of.

I admit I drop functions much more frequently in psql rather than in
my IDE/editor, but still I generally have the creation code handy.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it