pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with function
7+ messages / 4 participants
[nested] [flat]
* Help with function
@ 1970-01-01 00:00 Chris Hoover <revoohc@sermonaudio.com>
2005-06-09 20:03 ` Re: Help with function Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 1 reply; 7+ messages in thread
From: Chris Hoover @ 1970-01-01 00:00 UTC (permalink / raw)
To: pgsql-admin
I am trying to write a very generic function to be used for table auditing. I am kind of stuck on how to make the function use the column names I pull from the db. Is there anyway to do what I have below? My main concern is this segment:
if (new.columnName != old.columnName or
(old.columnName is null and new.columnName is not null) or
(old.columnName is not null and new.columnName is null)
) then
How can I get PostgreSQL to substitute the selected column name for the old.columnName/new.columnName?
Thanks,
Chris
CREATE OR REPLACE FUNCTION table_update_trig_func() RETURNS "trigger" AS
'
declare
/* setup the local variables */
loopRec record;
columnName varchar;
sqlStr varchar(2000);
begin
/* write out to the log file where we are */
raise notice \'in start of table_update_trig_func\';
/* setup the current sql string */
sqlStr := \'select attname as columnName \';
sqlStr := sqlStr || \'from pg_class, pg_attribute, pg_type \';
sqlStr := sqlStr || \'where pg_class.relname = \'\'table\'\' \';
sqlStr := sqlStr || \'and pg_attribute.attrelid = pg_class.oid \';
sqlStr := sqlStr || \'and pg_attribute.atttypid = pg_type.oid \';
sqlStr := sqlStr || \'and pg_type.typname not in (\'\'oid\'\',\'\'tid\'\',\'\'xid\'\',\'\'cid\'\',\'\'oidvector\'\') \';
sqlstr := sqlStr || \'order by attname \';
/* start the check of each column in the table */
for loopRec in execute sqlStr loop
raise notice \'value of loopRec.columnName is %\', loopRec.columnName;
columnName := loopRec.columnName;
if (new.columnName != old.columnName or
(old.columnName is null and new.columnName is not null) or
(old.columnName is not null and new.columnName is null)
) then
raise notice \'table_update_trig_func - calling insert_into_log function\';
select * from eedi.insert_into_clmchglog(<parameters>);
end if;
end loop;
return new;
end;
'
LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
1970-01-01 00:00 Help with function Chris Hoover <revoohc@sermonaudio.com>
@ 2005-06-09 20:03 ` Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2005-06-09 20:03 UTC (permalink / raw)
To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin
On Thu, Jun 09, 2005 at 06:17:24PM +0000, Chris Hoover wrote:
> I am trying to write a very generic function to be used for table auditing. I am kind of stuck on how to make the function use the column names I pull from the db. Is there anyway to do what I have below? My main concern is this segment:
>
> if (new.columnName != old.columnName or
> (old.columnName is null and new.columnName is not null) or
> (old.columnName is not null and new.columnName is null)
> ) then
>
> How can I get PostgreSQL to substitute the selected column name for the old.columnName/new.columnName?
Standard answer has been "can't do that in plpgsql". You may want to
try pl/perl, pl/python (does that support triggers?) or pl/tcl.
If performance is an issue, write the function in C.
--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"If it wasn't for my companion, I believe I'd be having
the time of my life" (John Dunbar)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Help with function
@ 2005-08-12 17:48 Chris Hoover <revoohc@gmail.com>
2005-08-12 18:36 ` Re: Help with function Chris Hoover <revoohc@gmail.com>
2005-08-12 18:57 ` Re: Help with function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Chris Hoover @ 2005-08-12 17:48 UTC (permalink / raw)
To: pgsql-admin
I need some help with how to define a function. I am trying to create
a function that I pass two records into. However, I can not seem to
get the syntax correct in the definition.
Exactly what I'm doing is having a rule on one of my views call a
function and pass the new.* and old.* records to the function. the
function will then handle the inserting, updating, and deleting from
the underlying tables.
I tried to define the function as function_name(new_rec view_name,
old_rec view_name), but it abends on the call.
function_name(new_rec view_name%rowtype, old_rec view_name%rowtype)
will not compile.
Can someone please provide some guidance on how to pass the records to
my new function?
thanks,
Chris
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
2005-08-12 17:48 Help with function Chris Hoover <revoohc@gmail.com>
@ 2005-08-12 18:36 ` Chris Hoover <revoohc@gmail.com>
2005-08-12 21:28 ` Re: Help with function Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Chris Hoover @ 2005-08-12 18:36 UTC (permalink / raw)
To: pgsql-admin
Ok, here is a bit more information, When I declare the function as
function(view_name, view_name) an try to update my view. I am getting
the following errors:
ERROR: record type has not been registered
CONTEXT: PL/pgSQL function "clmhdr_view_update" while storing call
arguments into local variables
My rule is doing select function_name(new.*, old.*).
What is pg trying say here, and how to I modify my function to fix this?
Thanks,
Chris
On 8/12/05, Chris Hoover <revoohc@gmail.com> wrote:
> I need some help with how to define a function. I am trying to create
> a function that I pass two records into. However, I can not seem to
> get the syntax correct in the definition.
>
> Exactly what I'm doing is having a rule on one of my views call a
> function and pass the new.* and old.* records to the function. the
> function will then handle the inserting, updating, and deleting from
> the underlying tables.
>
> I tried to define the function as function_name(new_rec view_name,
> old_rec view_name), but it abends on the call.
>
> function_name(new_rec view_name%rowtype, old_rec view_name%rowtype)
> will not compile.
>
>
> Can someone please provide some guidance on how to pass the records to
> my new function?
>
> thanks,
>
> Chris
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
2005-08-12 17:48 Help with function Chris Hoover <revoohc@gmail.com>
2005-08-12 18:36 ` Re: Help with function Chris Hoover <revoohc@gmail.com>
@ 2005-08-12 21:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-08-12 21:28 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> Ok, here is a bit more information, When I declare the function as
> function(view_name, view_name) an try to update my view. I am getting
> the following errors:
> ERROR: record type has not been registered
This is an internal bug. Are you running 8.0.3? If so, let's see a
complete test case --- if not, please see if updating fixes it.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
2005-08-12 17:48 Help with function Chris Hoover <revoohc@gmail.com>
@ 2005-08-12 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 22:24 ` Re: Help with function Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2005-08-12 18:57 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> I tried to define the function as function_name(new_rec view_name,
> old_rec view_name), but it abends on the call.
> function_name(new_rec view_name%rowtype, old_rec view_name%rowtype)
> will not compile.
> Can someone please provide some guidance on how to pass the records to
> my new function?
Not when you're being so parsimonious with details. Show us the full
text of the function and the exact error message you got.
Also, I forget which Postgres version you are using --- it matters.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
2005-08-12 17:48 Help with function Chris Hoover <revoohc@gmail.com>
2005-08-12 18:57 ` Re: Help with function Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-16 22:24 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-08-16 22:24 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> If I try to do an insert into the mytable_view, everything is happy.
> If i try to do an update or delete, I get the error:ERROR: record
> type has not been registered
> CONTEXT: PL/pgSQL function "mytable_view_update" while storing call
> arguments into local variables
Well, the good news is this was fixed some time ago in CVS tip ... the
bad news is that back-porting the fix to 8.0 doesn't look real
practical :-(. In 8.0 it'll only work if you're not using inheritance.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-08-16 22:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Help with function Chris Hoover <revoohc@sermonaudio.com>
2005-06-09 20:03 ` Alvaro Herrera <alvherre@surnet.cl>
2005-08-12 17:48 Help with function Chris Hoover <revoohc@gmail.com>
2005-08-12 18:36 ` Chris Hoover <revoohc@gmail.com>
2005-08-12 21:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-12 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 22:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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