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

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

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:

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