Обсуждение: how to drop function?

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.

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

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

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

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
>

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

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.

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

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?

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

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

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 ;)

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