Thread: 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;

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?

>     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;

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