Hello there
I have been trying to generate a dynamic querystring thats to be executed inside a trigger function and every time I use NEW.* pl/pgsql throws an error saying 'column * does not exist'. - CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
-
RETURNS trigger AS
-
$$
-
-
BEGIN
-
-
EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || NEW.*;
-
-
RETURN NULL;
-
END;
-
$$
-
LANGUAGE 'plpgsql'
Since this did not work I tried to alter it a bit by explicitly specifying the column names like - CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
-
RETURNS trigger AS
-
$$
-
-
BEGIN
-
-
EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || (NEW.id,NEW.accept,NEW.day,NEW.month,NEW.year);
-
-
RETURN NULL;
-
END;
-
$$
-
LANGUAGE 'plpgsql'
It works fine for non-character columns, I tried to insert values like "INSERT into matrix(id,accep t,day,month,yea r) values (4,'YES',3,3,20 07)", but pl/pgsql throws an error for 'YES'. This is because it ignores the quotes when I specify the column names explicitly.
Could anyone please help me out with this? And if you are wondering why the table names are different, they are partitioned tables so there is a child-parent relationship between them.
Any help on this issue would be appreciated!
Cheers
4 4512 rski 700
Recognized Expert Contributor
Try like this -
CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
-
RETURNS trigger AS$$
-
BEGIN
-
EXECUTE 'INSERT INTO matrix_' || NEW.month || (id,accept,day,month,year) values( ' || NEW.id ||','|| NEW.accept ||','|| NEW.day ||','|| NEW.month ||','|| NEW.year||')';
-
-
RETURN NULL;
-
END;
-
$$ LANGUAGE 'plpgsql'
-
Does it work for you?
Thanks for your resply rski. I was having a play with it and figured out the following works, - CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
-
RETURNS trigger AS $$
-
BEGIN
-
EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values(' || NEW.id ||,'''|| NEW.accept ||''',|| NEW.day ||,|| NEW.month ||,|| NEW.year||')';
-
RETURN NULL;
-
END;
-
$$ LANGUAGE 'plpgsql'
although I would like to know why NEW.* wont work. Its purely because I have around 65 columns and I dont want to go through each of them by hand.
I know something like the following is possible, - IF (NEW.month = 03 and NEW.year=2007) THEN
-
INSERT INTO landings_2007_03 values (NEW.*);
I use this for a different table, I would like to know if there is a way to achieve something like above when building a dynamic query with NEW.*
Cheers
rski 700
Recognized Expert Contributor
try function like this -
create or replace function instead_ins() returns trigger as $$
-
declare
-
r record;
-
t text;
-
x text;
-
y text;
-
begin
-
select NEW.* into r;
-
select r into x;
-
y=substring(x from 2 for length(x)-2);
-
t='table_prefix_'||NEW.column_name;
-
execute 'insert into '||t||' values( '||y||')';
-
return NULL;
-
-
end;
-
$$ language 'plpgsql';
-
is it helpful?
Thanks again for your reply rski.
The function you had posted is a cool work around although I get the following error ,
Syntax error at or near ","
insert into matrix_10 values(2,,,,,,, 2007,19,10,,,,, ,,,,,,name,,,,, ,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,, ,,,,,,,)
I guess its because record type is looking for data for all columns..?? Or is it me overlooking something?
Cheers
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: D. Dante Lorenso |
last post by:
I just wrote a PL/PGSQL function that is working, but I don't know
why it is...
I have a foreign key constraint defined on:
transaction.invoice_id --> invoice.invoice_id
But I did NOT state that it was DEFERRABLE. In this PL/PGSQL
function below, I update the transaction values and set them
to the invoice_id that does not yet exist in...
|
by: Karam Chand |
last post by:
Hello
I have been working with Access and MySQL for pretty
long time. Very simple and able to perform their jobs.
I dont need to start a flame anymore :)
I have to work with PGSQL for my companies current
project.
I have been able to setup postgresql in my rh box and
|
by: Postgresql |
last post by:
Hello,
I am unable to get on many of the postgresql mailing lists.
I usually get this message back with no attachment and no email comes
afterwards with a confirmation.
I've tried this over and over on multiple lists.
After a while I was able to get on pgsql-admin...but it took a few tries.
It's been a couple days now, so I'm assuming...
|
by: Graeme Hinchliffe |
last post by:
Hiya,
Not had much experience with tiggers under postgres but am liking them so
far.
My problem is this. I am writing an updates system, postgres holds the
master copy of the database, any changes made to this are logged in an
updates table which is monitored by a daemon, which if any updates are
spotted propigates them to the remote...
|
by: Henriksen, Jonas F |
last post by:
Hi,
I'm writing some simple triggers and functions for a postgres database, andI'm wondering how to go about to debug a pl/pgSQL-script. Is there a way to echo variable-content to screen, or to file? Or is there other, more advanced ways of debugging such scripts?
regards Jonas:))
---------------------------(end of...
| |
by: snpe |
last post by:
Is it down ?
regards
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
by: Tim Vadnais |
last post by:
Hi,
My boss wants to add some logging functionality to some of our tables on
update/delete/insert. I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this wing postgres pgSQL triggers.
We are given 10 automatically created variables. Some...
|
by: sysxperts |
last post by:
Hello,
Having an issue that is specific to PHP compiled with PGSQL support
with versions noted in subject line. I understand that there are many
variables to consider here but believe I have narrowed down the Apache
Client Cert failures to my PHP/PGSQL build.
1. Apache PHP without PGSQL works as expected using client
certificates
2. ...
|
by: ghe |
last post by:
Good afternoon to all,
I have another inquiry about PHP, I just wanted to ask how can i create TRIGGERS in PostgreSQL (PGSQL)
via PHP?
Does PHP offers other functions with the same functionality as of oci_parse() function? because base from what i have read, in order to use oci_parse(), you first need to have installed Oracle in your PC....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |