Thread: A trigger that acts on a column with a given column name

In a trigger function, I'm trying to refer to a column given by an argument
to the trigger function.

The trigger function fires on deletes and is responsible for deleting messages
that are referred to using foreign keys which are named differently in different
tables (thus the foreign key's column name as an argument).

Thus, in my trigger function, I need something like this:

DECLARE
  message_id INTEGER;
BEGIN
  message_id := quote_literal('OLD.' || TG_ARGV[0]);

This, of course, doesn't get me the value of the column named TG_ARGV[0] in
OLD, but instead just gives me an error because I'm trying to assign the
string value returned by quote_literal() to an integer.

So, my question is: how can I convince PostgreSQL that a string which I've
constructed is in fact the name of a column? I've tries some variation with
SELECT INTO, but I keep bumping into the same question ...

Any help much so appreciated.

 - Rowan

--
Morality is usually taught by the immoral.

On Thu, Jul 07, 2005 at 09:28:23PM +0200, BigSmoke wrote:
>
> In a trigger function, I'm trying to refer to a column given by an argument
> to the trigger function.

This comes up frequently -- search the archives for past discussion.
The usual answer is to write the function in a language like PL/Perl,
PL/Tcl, or PL/Python.  See the PL/Tcl documentation for an example:

http://www.postgresql.org/docs/8.0/static/pltcl-trigger.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Thu, Jul 07, 2005 at 09:28:23PM +0200, BigSmoke wrote:
> >
> > In a trigger function, I'm trying to refer to a column given by an argument
> > to the trigger function.
>
> This comes up frequently -- search the archives for past discussion.
> The usual answer is to write the function in a language like PL/Perl,
> PL/Tcl, or PL/Python.  See the PL/Tcl documentation for an example:
>
> http://www.postgresql.org/docs/8.0/static/pltcl-trigger.html

Thanks for pointing me towards the right answer. Now I know not to look at
PL/PGSQL for the functionality I desire. I will either implement my desires in a
different procedural language or adjust my desires :-)

It's one of those questions for which I couldn't figure out any keywords to
search for. I'm sorry for bringing up a FAQ, which is as embarrassing
as it should
be.

 - Rowan

--
Morality is usually taught by the immoral.

Hi,

  I messed around with the following problem and just want to let you know.

I have the following function:
    -    CREATE OR REPLACE FUNCTION
usp_inventaire_transaction_inserer("varchar", "varchar", int2,
"varchar", "varchar", "varchar", int4, "timestamp", "timestamp",
"numeric", "numeric", "varchar", "varchar", "varchar", "varchar",
"varchar", "timestamp")

If I explicitly cast the third argument to a smallint the function is found.

select * from usp_Inventaire_Transaction_Inserer('10000000', 'M',
3::INT2, 'S', 'toto', 'EN', 2,'2005-07-07', '2005-07-07', 0.0, 0.0,
'','', '', '', '', '2005-07-07');

If I remove the cast I got the followin error.

select * from usp_Inventaire_Transaction_Inserer('10000000', 'M', 3,
'S', 'toto', 'EN', 2,'2005-07-07', '2005-07-07', 0.0, 0.0, '','', '',
'', '', '2005-07-07');

ERROR:  function usp_inventaire_transaction_inserer("unknown",
"unknown", integer, "unknown", "unknown", "unknown", integer, "unknown",
"unknown", numeric, numeric, "unknown", "unknown", "unknown", "unknown",
"unknown", "unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

It seems Postgresql has problem guessing that 3 may fit in a small int.
I this an error?  Anyway it's just to let you know since I replace the
smallint with an integer and everything works  fine:-)

Have a great day

/David

Hi all,

I was juste wondering why the following code don't work:
 UPDATE gl SET gl.glnum = gl.glnum
ERROR:  column "gl" of relation "gl" does not exist

While the following works:
UPDATE gl SET glnum = glnum;

Query returned successfully: 177 rows affected, 281 ms execution time.

the TABLE.COLUMN is not in the SQL standard ?

Thanks
/David

On Fri, 8 Jul 2005, David Gagnon wrote:

> Hi all,
>
> I was juste wondering why the following code don't work:
>  UPDATE gl SET gl.glnum = gl.glnum
> ERROR:  column "gl" of relation "gl" does not exist
>
> While the following works:
> UPDATE gl SET glnum = glnum;
>
> Query returned successfully: 177 rows affected, 281 ms execution time.
>
> the TABLE.COLUMN is not in the SQL standard ?

For at least 92 (and I'm almost certain 99) not in the SET list.  It uses
column name (which is a plain identifier) rather than a column reference.

On Fri, Jul 08, 2005 at 09:59:03 -0400,
  David Gagnon <dgagnon@siunik.com> wrote:
> Hi all,
>
> I was juste wondering why the following code don't work:

Because the value being set is a column name from the table being updated
and you aren't allowed to qualify it with a table name.

You don't really want to use the table name of the right side either as
that will result in gl being joined to itself and will not give you the
results you expect. (This is using the implied from feature which is
enabled by default in 8.0.* or less, but will be disabled by default
in 8.1.)

> UPDATE gl SET gl.glnum = gl.glnum
> ERROR:  column "gl" of relation "gl" does not exist
>
> While the following works:
> UPDATE gl SET glnum = glnum;
>
> Query returned successfully: 177 rows affected, 281 ms execution time.
>
> the TABLE.COLUMN is not in the SQL standard ?
>
> Thanks
> /David
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 8 Jul 2005, David Gagnon wrote:
>> UPDATE gl SET gl.glnum = gl.glnum
>> ERROR:  column "gl" of relation "gl" does not exist
>>
>> the TABLE.COLUMN is not in the SQL standard ?

> For at least 92 (and I'm almost certain 99) not in the SET list.  It uses
> column name (which is a plain identifier) rather than a column reference.

More specifically, it'd be OK to write
    SET glnum = gl.glnum
but not as you have it.

Since 8.0 Postgres uses the above syntax to refer to assignment to a
subfield of a composite-type field, so that's why you get a complaint
about a nonexistent column instead of a flat-out syntax error.

            regards, tom lane

I have a construct where column has the same name .. and when I use the FROM clause  I get the following error:


create table test (
    id varchar(8)
);

create table test2 (
    id varchar(8)
);

update test set id=test2.id from test2 where id=test2.id;

ERROR:  column reference "id" is ambiguous


/David
P.S.: It's just an example .. I rename the column in the real statement to make it works.




Bruno Wolff III wrote:

On Fri, Jul 08, 2005 at 09:59:03 -0400, David Gagnon <dgagnon@siunik.com> wrote: 

Hi all,

I was juste wondering why the following code don't work:   

Because the value being set is a column name from the table being updated
and you aren't allowed to qualify it with a table name.

You don't really want to use the table name of the right side either as
that will result in gl being joined to itself and will not give you the
results you expect. (This is using the implied from feature which is
enabled by default in 8.0.* or less, but will be disabled by default
in 8.1.)
 

UPDATE gl SET gl.glnum = gl.glnum
ERROR:  column "gl" of relation "gl" does not exist

While the following works:
UPDATE gl SET glnum = glnum;

Query returned successfully: 177 rows affected, 281 ms execution time.

the TABLE.COLUMN is not in the SQL standard ?

Thanks
/David

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to     choose an index scan if your joining column's datatypes do not     match   

David Gagnon <dgagnon@siunik.com> writes:

> update test set id=test2.id from test2 where id=test2.id;
>
> ERROR:  column reference "id" is ambiguous

It's complaining about the second use of "id", which could mean either
"test2.id" (which would be a self-join) or "test.id" (which is what
you want).

-Doug

On 07/07/2005 02:28:23 PM, BigSmoke wrote:
> In a trigger function, I'm trying to refer to a column given by an
> argument
> to the trigger function.

I sometimes have a tendency to use the m4 macro processor
to write different trigger functions with the
correct column name substituted in where appropriate.
More generally, I write macros and use them when
writing my triggers.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein