how to drop function?

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:

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

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

this did not work.

this does not work.

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 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.

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