Thread: problem with insertion of serial id

i've been stuck on a problem and i'm hoping that someone out there can
help me out.  my problem deals with three tables and one sequence.  here
are some example tables

create table mom (

mom_id             serial            primary key
);

create table dad (
dad_id                serial             primary key
);

create table mom_dad(
mom_id                integer            referenced from mom.mom_id
dad_id                   integer            referenced from dad.dad_id
);

a trigger on mom sets off a function that inserts any relevent
information into dad(my real tables obviously have more columns).  my
problem occurs when i try to put another trigger on dad that calls a
function that inserts the id's of both tables into mom_dad.  my function
looks like this;


create function parents() returns opaque as '

declare
 m mom%ROWTYPE;
  d  dad%ROWTYPE;
md mom_dad%ROWTYPE;
mom_mom_id_seq%ROWTYPE;
d_id        integer;
m_id     integer;

begin

d_id   :=  new.d_id;
insert into mom_dad(m_id, d_id) values (currval('mom_mom_id_seq'), d_id);

return null;

end;

'language 'plpgsql';

create trigger family after insert or update on dad
for each row execute procedure parents();



when i run this i get a parse error at or near "assets_asset_id_seq".  i have also tried make a variable to represent
thecurrval by using a select into statement, but couldn't get it to work.  when i put a raise notice statement in the
functionand manually assign a value to m_id, it returns that value.  otherwise, i do not know how to return the correct
value(currvalof m_id).  i have looked at all docs on sequences and have not found an example that works correctly.  i
mustbe missing something.  if anyone can help me out or point me in the right direction i would greatly appreicate it.


thanks in advance,

mikez


--
Mike Z
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14

Michael Zouroudis <mzouroudis@idealcorp.com> writes:
> when i run this i get a parse error at or near "assets_asset_id_seq".

You need to double those quote marks, viz
    ... currval(''mom_mom_id_seq'') ...

Or you can backslash 'em (\').  The point is that the function body
begins life as a string literal, and so you have to get it through the
string-literal parser.

            regards, tom lane