function and passing the table name to be used with SQL stmnt
ok so my probelm is I have a simple function like this
CREATE OR REPLACE FUNCTION bookid(_tabela varchar) RETURNS integer AS $
$
DECLARE
_id integer := 0;
BEGIN
SELECT INTO _id id FROM _tabela order by id desc limit 1;
RETURN _id+1;
END;
$$ LANGUAGE plpgsql;
and the problem is i can run this function cos im passing string to
SQL statement and not the table object. hym what can I do about it to
have function that will give me next id number back;
SELECT INTO _id id FROM _tabela order by id desc limit 1;
ok I ve found that
EXECUTE 'SELECT id FROM ' || quote_ident(_tabela) ||' order by id desc
limit 1' INTO _id;
some other question I dont like to make a new topic so I will add it
here.
I ve modified my function to
CREATE OR REPLACE FUNCTION bookid(_tabela varchar) RETURNS integer AS $
$
DECLARE
_id integer := 0;
BEGIN
--insert bez id wyzwala nextwal na tabela_columna_seq
EXECUTE 'insert into '|| quote_ident(_tabela) || '(idjm, waga,
stawka_vat) values(0, 0, '''')';
select into _id lastval();
RETURN _id;
END;
$$ LANGUAGE plpgsql;
and is that safe operation, can I rely on lastval() that it will
return value of id that was inserted before?
can it happened that other user will insert something between my
insert and select?
kamiseq <kamiseq@gmail.com> writes:
select into _id lastval();
and is that safe operation, can I rely on lastval() that it will
return value of id that was inserted before?
You want to use currval() with the sequence name.
can it happened that other user will insert something between my
insert and select?
currval() specifically written to be safe in this case.
-Doug