Skip site navigation (1) Skip section navigation (2)

Re: BUG #1875: Function parameter names clash with table column names

From: David Fetter <david(at)fetter(dot)org>
To: Byron <byron(dot)hammond(at)westnet(dot)com(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1875: Function parameter names clash with table column names
Date: 2005-09-10 20:10:16
Message-ID: 20050910201016.GA16405@fetter.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sat, Sep 10, 2005 at 05:46:44PM +0100, Byron wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:      1875
> Logged by:          Byron
> Email address:      byron(dot)hammond(at)westnet(dot)com(dot)au
> PostgreSQL version: 8.0.3
> Operating system:   Windows XP
> Description:        Function parameter names clash with table column names
> Details: 
> 
> -----------------------------
> Error Details from PgAdminIII
> -----------------------------
> ERROR:  syntax error at or near "$1" at character 28
> QUERY:  INSERT INTO test_table_0 ( $1 ,  $2 ,  $3 ) VALUES ( $4 ,  $5 ,  $6
> )
> CONTEXT:  PL/pgSQL function "test_function_0" line 2 at SQL statement
> 
> ------------
> Problem Code
> ------------
> CREATE TABLE test_table_0 (
> 	a	varchar(255),
> 	b	varchar(255),
> 	c	varchar(255)
> );
> 
> CREATE OR REPLACE FUNCTION test_function_0(a varchar, b varchar, c varchar)
> RETURNS INTEGER AS $$
> BEGIN
> 	INSERT INTO test_table_0 (a, b, c) VALUES (a, b, c);
> 	RETURN 1;
> END
> $$ LANGUAGE 'plpgsql';

This should read something like

CREATE OR REPLACE FUNCTION test_function_0(in_a varchar, in_b varchar, in_c varchar)
RETURNS INTEGER AS $$
BEGIN
	INSERT INTO test_table_0 (a, b, c) VALUES (in_a, in_b, in_c);
    IF FOUND THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END
$$ LANGUAGE 'plpgsql';

> 
> SELECT test_function_0('a', 'b', 'c');
> 
> DROP FUNCTION test_function_0(varchar, varchar, varchar);
> DROP TABLE test_table_0;
> 
> -------------------
> Additional Comments
> -------------------
> It appears in this case that the parameter names of the function
> cannot be the same as the column names used by the INSERT statement
> -- clashes.

This is not really a bug.  If the things really are different, in this
case function input parameters and column names, they should also have
different names.  PL/PgSQL merely enforces this :)

Cheers,
D
-- 
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-09-10 23:33:59
Subject: Re: BUG #1865: isinf wrongly dectected under Solaris 9
Previous:From: Peter EisentrautDate: 2005-09-10 19:51:45
Subject: Re: BUG #1874: Non-Execute Privileges enforced on grant

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group