Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with function

Help with function

From
Chris Hoover
Date:
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

Re: Help with function

From
Chris Hoover
Date:
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
>

Re: Help with function

From
Tom Lane
Date:
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

Re: Help with function

From
Tom Lane
Date:
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

Re: Help with function

From
Tom Lane
Date:
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