pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steffers <stef@chronozon.dyndns.org>
To: pgsql-interfaces@postgresql.org
Subject: Plpgsql Triggers and Variable Tablenames
Date: Thu, 25 May 2000 17:28:00 -0500 (CDT)
Message-ID: <20000525222800.E5E911F57C@chronozon.dyndns.org> (raw)


Hello everyone, 
	I hope that this is the correct forum in which to post the
question I have.

	Currently I have code which tries to do the following:

CREATE FUNCTION upd_history_no_location()
RETURNS opaque
AS 'BEGIN
        new.history_id=SELECT (max(history_id)+1) FROM client
            			 where client.CLIENT_ID=new.CLIENT_ID;
    END;'
LANGUAGE 'plpgsql';

	I plan on tying this trigger onto the 'client' table on
an INSERT condition. I will be using the same 'basic' trigger on 70-80
tables 
or so. To stop pointless re-iteration of code therefore I tried to create a
dynamic trigger as follows :

CREATE FUNCTION upd_history_no_location(text)
RETURNS opaque
AS 'BEGIN
        new.history_id=SELECT (max(history_id)+1) FROM $1
            			 where $1.CLIENT_ID=new.CLIENT_ID;
      END;'
LANGUAGE 'plpgsql';

	Needless to say, this didnt work. It seems to CREATE
okay, but when I try to trigger the action. urm. no. nice error message.
(and each table will have the field CLIENT_ID, and history_id, thats
not really an issue =)

	I have been told that this will work in tcl (and infact that is
the way it is working at the minute), seeing as how everything is in
python though (the program that ties into the database), I would prefer
to stick to two languages (python/[plpg]sql) and tcl doesnt really sit
well with me as a language (jst a personal bias from a long ago school
lecture).

	So, the question is, can I do this somehow under plpgsql
or can I raise this as a feature request to the maintener ? (assuming
he/she is here and reading the list).

	many thanks,
	Stefs.


(p.s because of the structure of the database, i cant use OID's or
 sequence id's, as the 'uniqueness' of this counter is also tied into
 a clients number and a clients workorder number. (dont ask ;))




Message-ID: <20000525222800.E5E911F57C@chronozon.dyndns.org>
Permalink:  ../20000525222800.E5E911F57C@chronozon.dyndns.org/
Also on:    postgresql.org/message-id/20000525222800.E5E911F57C@chronozon.dyndns.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-interfaces@postgresql.org
  Cc: stef@chronozon.dyndns.org
  Subject: Re: Plpgsql Triggers and Variable Tablenames
  In-Reply-To: <20000525222800.E5E911F57C@chronozon.dyndns.org>
  References: <20000525222800.E5E911F57C@chronozon.dyndns.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox