Thread: deleting function
Dear Friends
I have table called below
I need to delete the record by using id value of the table
For that I wrote below function
create or replace function newd(id integer ) returns void as $$
begin
delete from testing where id=$1;
end;
$$
language 'plpgsql';
Here I call the function as " select newd(100) "
But it is deleting every things from the table
My doubt is , why it was deleting every thing , even I gave the
single value in function .
pavunkumar=> \d testing;
Table "hospital.testing"
Column | Type | Modifiers
---------+-------------------+-----------
id | integer | not null
name | character varying |
address | character varying |
Indexes:
"testing_pkey" PRIMARY KEY, btree (id)
pavunkumar <pavun.bks@gmail.com> writes:
> create or replace function newd(id integer ) returns void as $$
^^
> begin
> delete from testing where id=$1;
^^
> end;
> $$
> language 'plpgsql';
Don't use the same name for a parameter as you use for a table column
referenced in the function. The above is equivalent to
"delete... where $1=$1", ie, delete everything.
regards, tom lane
On Fri, Apr 24, 2009 at 11:55:48AM -0400, Tom Lane wrote: > pavunkumar <pavun.bks@gmail.com> writes: > > create or replace function newd(id integer ) returns void as $$ > > delete from testing where id=$1; > > Don't use the same name for a parameter as you use for a table column > referenced in the function. I've found it helps to have a standard naming convention here; I only use identifiers starting with an underscore (i.e. "_") for function parameters and local variables. Column names always start with a lower case alphabetic letter. Other people will obviously have different ways of dealing with the ambiguity, but this has worked well for me so far. -- Sam http://samason.me.uk/
On 24/04/2009 14:03, pavunkumar wrote: > create or replace function newd(id integer ) returns void as $$ > begin > delete from testing where id=$1; > end; I'd guess it's because the column name, "id", is the same as the argument name - plpgsql thinks that "id" in the DELETE statement is the argument, so in effect you're doing - delete from testing where true; - with the results you saw. Give the argument a different name and you should be OK. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Apr 24, 8:55 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > pavunkumar <pavun....@gmail.com> writes: > > create or replace function newd(id integer ) returns void as $$ > > ^^> begin > > delete from testing where id=$1; > > ^^ > > > end; > > $$ > > language 'plpgsql'; > > Don't use the same name for a parameter as you use for a table column > referenced in the function. The above is equivalent to > "delete... where $1=$1", ie, delete everything. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Dear Friend Whatever your saying right , But why the function not saying error.... ? that is my doubt... this is what I want to clarify....!
On Apr 25, 2009, at 5:19 AM, pavunkumar wrote: > Dear Friend > > Whatever your saying right , But why the function > not saying error.... ? > that is my doubt... this is what I want to > clarify....! Because it's a valid comparison, just not the one you wanted. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49f2b93a129742098520748!
On Fri, Apr 24, 2009 at 12:15 PM, Sam Mason <sam@samason.me.uk> wrote:
> On Fri, Apr 24, 2009 at 11:55:48AM -0400, Tom Lane wrote:
>> pavunkumar <pavun.bks@gmail.com> writes:
>> > create or replace function newd(id integer ) returns void as $$
>> > delete from testing where id=$1;
>>
>> Don't use the same name for a parameter as you use for a table column
>> referenced in the function.
>
> I've found it helps to have a standard naming convention here; I only
> use identifiers starting with an underscore (i.e. "_") for function
> parameters and local variables. Column names always start with a lower
> case alphabetic letter. Other people will obviously have different ways
> of dealing with the ambiguity, but this has worked well for me so far.
I absolutely think this ('_' prefix) is the way to go. I make some
exceptions for locals, like n for number, etc. Some people argue for
using in_variable notation, but why use three characters when one is
good enough?
OTOH, I don't camel case columns. Can't please everyone... :-) Also,
to the OP, I'd really advise against naming columns 'id'...it creates
a mess. If you have a table foo, name column 'foo_id'. Less name
conflicts, cleaner queries.
merlin
On Fri, Apr 24, 2009 at 08:19:37PM -0700, pavunkumar wrote:
> On Apr 24, 8:55 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> > Don't use the same name for a parameter as you use for a table column
> > referenced in the function. The above is equivalent to
> > "delete... where $1=$1", ie, delete everything.
>
> Whatever your saying right , But why the function not saying error....
> ? that is my doubt... this is what I want to clarify....!
This is the same as most imperative programming languages; for example
in C:
{
int id = 1;
{
int id = 5;
printf ("id = %i\n", id);
}
printf ("id = %i\n", id);
}
Will print out 5 followed 1. In C it was considered useful to allow
inner blocks of code to have variables of the same name as outer blocks
and the actual name resolution would proceed from the inner-most block
outwards until a matching definition is found. This convention was
invented before C and is used in most languages I'm aware of.
PL/pgSQL follows a similar convention; unqualified names are looked
up first against the function's parameters and then defaults back to
looking for column names. It could be argued that it would be nice if
you got an error in the case of ambiguity like above, but PG doesn't
currently do that.
If you'd written your delete command as:
DELETE FROM testing WHERE testing.id = id;
or better:
DELETE FROM testing t WHERE t.id = id;
then it would do what you're expecting.
Hope that helps
--
Sam http://samason.me.uk/
On 2009-04-24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > pavunkumar <pavun.bks@gmail.com> writes: >> create or replace function newd(id integer ) returns void as $$ > ^^ >> begin >> delete from testing where id=$1; > ^^ >> end; >> $$ >> language 'plpgsql'; > > Don't use the same name for a parameter as you use for a table column > referenced in the function. The above is equivalent to > "delete... where $1=$1", ie, delete everything. would this: delete from testing where "id"=$1; be safe?
On 28/04/2009 10:56, Jasen Betts wrote: > would this: > > delete from testing where "id"=$1; > > be safe? No - you haven't really changed anything - the double-quotes just prevent Postgres folding mixed/upper-case identifiers to lower-case. Why not just change the name of the argument as others have suggested? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------