Thread: sequence values question

sequence values question

From

mmiranda@americatel.com.sv

Date:

10 May 2005, 19:46:34

Hi, how can i know the values generated by a column of type serial?
I mean, i have the following table

productos
(
  id serial,
  desc varchar(50)
)

select * from productos;

+-----+------------+
| id  | desc       |
+-----+------------+
|   1 | ecard1     |
|   2 | ecard2     |
|   3 | ecard3     |
|   4 | ecard4     |
|   5 | ecard5     |
+-----+------------+

I insert a row using a SP, i want to return the id and desc of the new
product in the table.
this is an example of the hypothetical SP

CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
AS $$
DECLARE
  vdesc alias for $1;
BEGIN
    INSERT INTO productos (desc) VALUES (vdesc);
    RETURN (new id ???) || ',' || vdesc;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;


I know i can get the last value using currval(text), and add 1 to the next
values, is this the only way?, what if i want to insert several products?,
should i  return a record ?
thanks

Re: sequence values question

From

Franco Bruno Borghesi

Date:

10 May 2005, 20:07:33

just obtain the next value from the sequence first, then do the insert:

CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
AS $$
DECLARE
  vdesc alias for $1;
new_id INTEGER;
BEGIN
        SELECT nextval('sequence_name_here') INTO new_id;
        INSERT INTO productos (id, desc) VALUES (new_id, vdesc);
        RETURN (new_id) || ',' || vdesc;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

2005/5/10, mmiranda@americatel.com.sv <mmiranda@americatel.com.sv>:

Hi, how can i know the values generated by a column of type serial?
I mean, i have the following table

productos
(
  id serial,
  desc varchar(50)
)

select * from productos;

+-----+------------+
| id  | desc       |
+-----+------------+
|   1 | ecard1     |
|   2 | ecard2     |
|   3 | ecard3     |
|   4 | ecard4     |
|   5 | ecard5     |
+-----+------------+

I insert a row using a SP, i want to return the id and desc of the new
product in the table.
this is an example of the hypothetical SP

CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
AS $$
DECLARE
  vdesc alias for $1;
BEGIN
        INSERT INTO productos (desc) VALUES (vdesc);
        RETURN (new id ???) || ',' || vdesc;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

I know i can get the last value using currval(text), and add 1 to the next
values, is this the only way?, what if i want to insert several products?,
should i  return a record ?
thanks

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend