[SQL] Please help! Functions passing records between them

[Alla]

Guys;

I am begging for your help again.

I can't find a solution to my problem.

I am porting a complex system from Oracle to PostgreSQL and I need to
implement the following:

function 1 does some processing and returns a record (I can declare it
as a row in a view)
function 2 uses func1 to get that record and does some more processing

My problem is that even if I can return a record from my function 1,
function 2 does not read it properly

Here is an example:
create view my_view
  as select null as type, null as value, null as timestamp;  -- this
is how I "declare" the user-defined data structure (I could not find
any other way)

create function func1()
returns my_view as '
declare
   my_record   my_view%rowtype;
begin
   .
   .
   my_record.type := ''AAA'';
   my_record.value := 25;
   my_record.timestamp := now();   -- this is for simplicity
  
   return my_record;
end;
' LANGUAGE 'plpgsql';

create function func2()
returns varchar as '
declare
   my_record   my_view%rowtype;
begin
   select func1() into my_record;

   return my_record.type;
end;
' LANGUAGE 'plpgsql';

   
It compiles and runs fine, except that it does not return what it's
supposed to. It gives me some strange huge number, which I assume is
some kind of OID


I know that there are quite a few gurus of PostgreSQL out there -
please help me solve this problem. May be my whole approach is wrong,
but I need to be able to accomplist this: pass some kind of
user-defined structures between function

Thank you so much for your help

Alla Gribov

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Function returning record

[Alla]

Is it possible in PostgreSQL to write a function that would return a
record type.

What I need is something like this:

create function my_func(varchar)
return record as '
declare
   my_rec   record;
begin
   select null as field1, null as field2
   into my_rec;

    some processing to populate the actual values of the record

   return my_rec;
end;
' LANGUAGE 'plpgsql';


I get the following when I try to compile this:
NOTICE:  ProcedureCreate: return type 'record' is only a shell

and following when I try to execute it (even though I am not sure how
to execute this at all);
ERROR:  fmgr_info: function 0: cache lookup failed

Please help.

Thanks a lot in advance

Alla

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] rowtype and ecpg

[Alla]

I have a function that takes as a parameter ROWTYPE:

create or replace function test_func(test_table) 
returns varchar as '
declare
   lv_return   varchar;
begin
   ..
   return lv_return;
end;
' LANGUAGE 'plpgsql';

How do I call this function from the C program (ecpg)? How my
declaration should look like?

I trued structure and got error: Too many arguments

Thanks for your help

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] rowtype and ecpg

[Alla]

My problem is a "rowtype" variable. 

I don't know how to declare a structure or anything else to pass
information to the function, which input parameter has a type of
"rowtype" (I have no problem retrieving any output - varchar or
refcursor)

Thanks

Alla



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly