I want to create a sequence for each row created in the table account, like os_1, os_2, etc...
How can I get the id of this new row and insert it on the name of the sequence?
CREATE OR REPLACE FUNCTION public.create_os_seq() RETURNS TRIGGER AS $$
#variable_conflict use_variable
BEGIN
--CREATE SEQUENCE seqname;
EXECUTE format('CREATE SEQUENCE os_', NEW.id);
return NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER create_os_seq AFTER INSERT ON account FOR EACH ROW EXECUTE PROCEDURE create_os_seq();
Table account
id INT AUTO_INCREMENT
nane VARCHAR
After creating a sequence i´ll put its number in the OS table
table os
id INT
account_id INT
From your question I assume you want to take care of self incrementing values?.. Postgres uses shortcut SERIAL instead of AUTO_INCREMENT, just create table like:
CREATE TABLE so79 (id bigserial primary key, col text);
That will automatically create sequence for you and assign its value as default for column id. Basically will make it smth like AUTO_INCREMENT. You don't have to use trigger to increment values...
There is no way that creating a sequence for each row is a good idea. Instead, tell us what you're trying to do.
My guess is you need a waterline indicator, a high point you intend to increment on some action. Just use an integer.
CREATE TABLE foo (
foo_id serial,
max_seen int
);
Now you can do whatever with triggers on other tables and such to increment max_seen.