pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help 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