[GENERAL] How to insert into 2 tables from a view?
Hi, I am having a problem trying to figure out. I have two tables behind a view and am trying to figure out how to create the correct insert rule so that inserting into the view is redirected to the two tables. I thought I had is solved using a stored procedure, but doing an insert into view ... returning id causes the insert to fail with this error: ERROR: cannot perform INSERT RETURNING on relation orig_view HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause We are running pg 9.0 and I think this version of PG is the bottleneck to getting this done. Does anyone know how to get around it? Below is a basic example demonstrating what we are wanting to do. CREATE TABLE table1 ( table1_id SERIAL PRIMARY KEY, table1_field1 TEXT ); CREATE TABLE table2 ( table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON DELETE CASCADE, table2_field1 TEXT ); CREATE VIEW orig_table AS SELECT table1_id, table1_field_1, table2_field1 FROM table1 JOIN table2 USING (table1_id); CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1 text, in_table2_field1 text) RETURNS SETOF orig_table LANGUAGE plpgsql AS $BODY$ DECLARE v_table1_id table1.table1_id%TYPE BEGIN INSERT INTO table1 ( table1_id, table1_field1 ) VALUES ( in_table1_id, in_table1_field1 ) RETURNING table1_id INTO v_table1_id; INSERT INTO table2 ( table1_id, table2_field1 ) VALUES ( v_table_id, in_table2_field1 ); RETURN QUERY SELECT table1_id, table1_field1, table2_field1 FROM orig_table WHERE table1_id = v_table1_id; END; $BODY$; CREATE RULE orig_table_insert_rule AS ON INSERT TO orig_table DO INSTEAD SELECT orig_table_insert(NEW.table1_id, NEW.table1_field1, NEW.table2_field1); Thanks, Chris
Re: [GENERAL] How to insert into 2 tables from a view?
Sorry, in my haste to get the example out, a couple of typo's where in the
sql.
Correct sql:
BEGIN;
CREATE TABLE table1 (
table1_id SERIAL PRIMARY KEY,
table1_field1 TEXT
);
CREATE TABLE table2 (
table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON
DELETE CASCADE,
table2_field1 TEXT
);
CREATE VIEW orig_table AS
SELECT table1_id, table1_field1, table2_field1
FROM table1
JOIN table2 USING (table1_id);
CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1
text, in_table2_field1 text)
RETURNS SETOF orig_table
LANGUAGE plpgsql
AS
$BODY$
DECLARE
v_table1_id table1.table1_id%TYPE;
BEGIN
INSERT INTO table1 (
table1_id, table1_field1
) VALUES (
COALESCE(in_table1_id, NEXTVAL('table1_table1_id_seq')),
in_table1_field1
)
RETURNING table1_id
INTO v_table1_id;
INSERT INTO table2 (
table1_id, table2_field1
) VALUES (
v_table1_id, in_table2_field1
);
RETURN QUERY SELECT table1_id, table1_field1, table2_field1
FROM orig_table
WHERE table1_id = v_table1_id;
END;
$BODY$;
CREATE RULE orig_table_insert_rule AS
ON INSERT
TO orig_table
DO INSTEAD
SELECT orig_table_insert(NEW.table1_id, NEW.table1_field1,
NEW.table2_field1);
COMMIT;
Problem query:
insert into orig_table (table1_field1, table2_field1) values ('field1',
'field2') returning table1_id;
On Tue, Dec 23, 2014 at 1:46 PM, Chris Hoover revo...@gmail.com wrote:
Hi,
I am having a problem trying to figure out.
I have two tables behind a view and am trying to figure out how to create
the correct insert rule so that inserting into the view is redirected to
the two tables. I thought I had is solved using a stored procedure, but
doing an insert into view ... returning id causes the insert to fail with
this error:
ERROR: cannot perform INSERT RETURNING on relation orig_view
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a
RETURNING clause
We are running pg 9.0 and I think this version of PG is the bottleneck to
getting this done. Does anyone know how to get around it? Below is a
basic example demonstrating what we are wanting to do.
CREATE TABLE table1 (
table1_id SERIAL PRIMARY KEY,
table1_field1 TEXT
);
CREATE TABLE table2 (
table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON
DELETE CASCADE,
table2_field1 TEXT
);
CREATE VIEW orig_table AS
SELECT table1_id, table1_field_1, table2_field1
FROM table1
JOIN table2 USING (table1_id);
CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1
text, in_table2_field1 text)
RETURNS SETOF orig_table
LANGUAGE plpgsql
AS
$BODY$
DECLARE
v_table1_id table1.table1_id%TYPE
BEGIN
INSERT INTO table1 (
table1_id, table1_field1
) VALUES (
in_table1_id, in_table1_field1
)
RETURNING table1_id
INTO v_table1_id;
INSERT INTO table2 (
table1_id, table2_field1
) VALUES (
v_table_id, in_table2_field1
);
RETURN QUERY SELECT table1_id, table1_field1, table2_field1
FROM orig_table
WHERE table1_id = v_table1_id;
END;
$BODY$;
CREATE RULE orig_table_insert_rule AS
ON INSERT
TO orig_table
DO INSTEAD
SELECT orig_table_insert(NEW.table1_id, NEW.table1_field1,
NEW.table2_field1);
Thanks,
Chris
[GENERAL] PGCon 2008, Are you going?
Hey everyone, Are you going to PGCon 2008? If so, where are you staying. My company has decided to send me, and I am just wondering where to stay. Based on price, it looks like on the University of Ottawa campus, or the Quality Inn are my two choices (the third hotel is more than I am allotted to spend). Anyway, I was just wondering where most of the attendees stay so I can stay in the same location and get the most from the conference. Thanks, Chris -- Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY. Visit http://colafuelguy.mybpi.com and join the revolution!
[GENERAL] Help with date math
I need some help. I am trying to replicate a function from Sybase ASA, and am having difficulty. I need to be able to subtract 2 date (or timestamps) and return the results expressed in days, weeks, month, quarters, or years. How do I do this? I believe Postgres is returning the number of days when you subtract to days. i.e. postgres=# select current_date - '2007/01/01'; ?column? -- 200 (1 row) However, I can not figure out how to get the results expressed as 7 months, or 2 quarters, x weeks, or 0 years. I have tried to use date_part, but since the subtraction is returning an integer, it is not happy. Any help would be greatly appreciated. Chris P.S. I'm trying to replicate Sybase's datediff function.
Re: [GENERAL] 8.2.4 signal 11 with large transaction
On 7/20/07, Tom Lane [EMAIL PROTECTED] wrote: I guess what we need to do is hack the emergency-recovery path for error-during-error-processing such that it will prevent trying to print a very long debug_query_string. Maybe we should just not try to print the command at all in this case, or maybe there's some intermediate possibility like only printing the first 1K or so. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster I know my 2 cents are not worth that much, but as a DBA, I would really like for you to print at least some of the string causing the abend. This would greatly assist in the tracing of the offending query. Chris
Re: [GENERAL] unexpected shutdown
On 6/18/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: My database has shutdown several times in the last couple days. I have no idea why. I am running centos and I have not rebooted the server or made any configuration changes. I am running postgres 8.2 and it has been stable since I installed it about 5 months ago. The databases crashes and so my software application goes down. When I restart my application everything seems to work fine. But then it crashes again, something appears to be corrupt. Here are my logs: LOG: server process (PID 501) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-17 10:55:32 PDT LOG: checkpoint record is at 0/72F41748 LOG: redo record is at 0/72F41748; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2638157; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/72F41790 LOG: redo is not required LOG: database system is ready LOG: server process (PID 13904) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-18 10:09:51 PDT LOG: checkpoint record is at 0/73609D18 LOG: redo record is at 0/73609D18; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2645768; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shut
[GENERAL] how to get number of minutes?
If I subtract 2 timestamps, how do I get the results returned as the total number of minutes.examplenow() - (now()-'2 hours'::interval) = 2:00:00 -- I need the result returned as 120 minutes.Thanks, Chris
[GENERAL] Fwd: Help with function
I did not see this go through.Chris-- Forwarded message --From: Chris Hoover [EMAIL PROTECTED] Date: Oct 3, 2006 4:49 PMSubject: Help with functionTo: pgsql-general@postgresql.orgI need some help with writing a plpgsql function. I want to return multiple items from the function. How do I do this? Here is my attempt at the function (note, this is a simple example that could obviously be done via a view, but I am trying to learn more about writing plpgsql functions): create or replace function dba.active_transactions_by_db() returns setof integer pg_stat_activity.datname%TYPE as$BODY$declare dbName varchar; activeTransactions integer; countRec record; begin for countRec in select count(1) as cnt, datname from pg_stat_activity group by datname loop return next countRec; end loop; return countRec;end;$BODY$language plpgsql;
[GENERAL] Help with function
I need some help with writing a plpgsql function. I want to return multiple items from the function. How do I do this?Here is my attempt at the function (note, this is a simple example that could obviously be done via a view, but I am trying to learn more about writing plpgsql functions): create or replace function dba.active_transactions_by_db() returns setof integer pg_stat_activity.datname%TYPE as$BODY$declare dbName varchar; activeTransactions integer; countRec record; begin for countRec in select count(1) as cnt, datname from pg_stat_activity group by datname loop return next countRec; end loop; return countRec;end;$BODY$language plpgsql;
[GENERAL] How to trace index to table?
I'm trying to build some queries to gather metrics on my PG database. When looking at pg_class and pulling a row that is an index, how do I use sql to pull the table the index belongs to?Thanks,Chris
[GENERAL] Fwd: How to convert a string to bytea?
-- Forwarded message --From: Chris Hoover [EMAIL PROTECTED]Date: Aug 30, 2006 12:22 PM Subject: How to convert a string to bytea?To: pgsql-admin@postgresql.org pgsql-admin@postgresql.org I am in need of some help. I need to use the encode function against a text string in one of my pg/plsql functions. However, the encode function expects a bytea field to be passed to it. How do I convert my text string into a bytea? Thanks,Chris
Re: [GENERAL] [8.1.4] Create index on timestamp fails
It appears that 8.1 is stricter on checking the type of function. Look at your user_tracking function. It is probably set as volatile. You need to change it to be immutable.This should fix the issue.Chris
On 8/21/06, Arturo Perez [EMAIL PROTECTED] wrote:
Hi all,
Using postgresql 8.1.4
I have a table with an column:
entry_date | timestamp with time zone| not null
And when I try to create an index on it like so:
create index entry_date_idx on user_tracking(date_part('year',entry_date));
I get a
ERROR: functions in index _expression_ must be marked IMMUTABLE
According to the mailing lists, this has been working since 7.4. What am I doing wrong?
tia,
arturo
[GENERAL] Why is default value not working on insert?
I have the following table:CREATE TABLE code_source( csn_src int4 NOT NULL, csn_type varchar(8) NOT NULL, cs_code varchar(15) NOT NULL, cs_desc_short varchar(30), cs_desc_long text,
cs_remarks varchar(20), cs_work_flag char(1), cs_status char(1), cs_manual_key bool NOT NULL DEFAULT false, cs_create timestamp NOT NULL DEFAULT now(), cs_live date NOT NULL, cs_last_mod timestamp,
cs_expire date, cs_last_mod_user varchar(12), CONSTRAINT code_source_pkey PRIMARY KEY (csn_src, csn_type, cs_code), CONSTRAINT code_source_fkey FOREIGN KEY (csn_src, csn_type) REFERENCES code_source_name (csn_src, csn_type) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT) WITHOUT OIDS;As you can see, cs_create is set to not null with a default value of now().However, when I run the following insert, it errors stating cs_create can not be null. Why is the default not working?
insert into code_source (csn_src, csn_type, cs_code, cs_desc_short, cs_desc_long, cs_remarks, cs_work_flag, cs_status, cs_manual_key, cs_create, cs_live, cs_last_mod, cs_expire, cs_last_mod_user) values ('132', 'CODE', '49', 'Some Code', null, 'NEWCODE', null, null, false, to_date(null,'mmdd'), to_date('19000101','mmdd'), to_date('20040318','mmdd'), to_date('1231','mmdd'), 'MSBIUSERID');
ERROR: null value in column cs_create violates not-null constraintThe reason for the null being passed to to_date is this is on of almot 90k lines of data we are trying to load, and the script was built to generate this code. Since to_date(null,'mmdd') returns a null, why is the default not working?
ChrisPG 8.1.3RH AS 4
[GENERAL] Join Question
Question,What is the difference between left join, and left outer join?I know the difference between inner and outer joins, but I was thinking that left join == inner join. But from what I am now seeing, it appears that PG is equating left join to left outer join. Is this correct? Thanks,Chris
[GENERAL] Can you run out of oids?
Somewhat silly question, but is it possible to run out of OID's?Since we upgraded to 8.1.3, I noticed that I can create tables without an oid column. I am wondering if I should consider trying to rebuild the existing tables to be built without OID. If it is possible to run out of OID's, how can you tell how close you are to the edge.Thanks,ChrisRH4.0PG8.1.3
[GENERAL] Splitting Timestamps
I have several columns in my database that are timestamps. My developers are asking me how to split the timestamp so that they can look at either the date or at the time portion.I know I can do a select to_date(now(),'-mm-dd') and it will return the date. However, how do I get the time? Also, is this the proper way to get the date portion of a timestamp? Thanks,Chris
[GENERAL] tsearch2
I have some general questions on tsearch2. How do you set it up to search for how now brown cow vs how now brown cow? The first example is looking for the exact phrase of words, while the second is just looking for all of the works. Secondly, if you want to split a text field based on multiple delimiters (i.e. a text line with johnny bravo,123 bravo ln,apple), do you have to write the parser, or is there a way to pass the parser a ' ' and a ',' as the word delimiters? Thanks,Chris
