Обсуждение: triggers and execute...
OK, I'm hitting a wall here. I've written this trigger for partitioning:
create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
part text;
q text;
BEGIN
part = to_char(new."timestamp",'YYYYMMDD');
q = 'insert into page_access_'||part||' values (new.*)';
execute q;
return null;
END;
$$ language plpgsql;
drop trigger page_access_insert_trigger on page_access cascade;
create trigger page_access_insert_trigger before insert or update on page_access
for each row execute procedure page_access_insert_trigger();
When I create it and try to use it I get this error:
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)"
PL/pgSQL function "page_access_insert_trigger" line 7 at EXECUTE statement
If I rewrite it to just write to that table:
create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
part text;
q text;
BEGIN
-- part = to_char(new."timestamp",'YYYYMMDD');
-- q = 'insert into page_access_'||part||' values (new.*)';
-- execute q;
insert into page_access_20090427 values (new.*);
return null;
END;
$$ language plpgsql;
It works. So, how am I supposed to run it with dynamic table names?
On Mon, Apr 27, 2009 at 2:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> OK, I'm hitting a wall here. I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
> part text;
> q text;
> BEGIN
> part = to_char(new."timestamp",'YYYYMMDD');
> q = 'insert into page_access_'||part||' values (new.*)';
> execute q;
> return null;
> END;
> $$ language plpgsql;
> drop trigger page_access_insert_trigger on page_access cascade;
> create trigger page_access_insert_trigger before insert or update on page_access
> for each row execute procedure page_access_insert_trigger();
>
>
> When I create it and try to use it I get this error:
> ERROR: NEW used in query that is not in a rule
> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)"
> PL/pgSQL function "page_access_insert_trigger" line 7 at EXECUTE statement
OK, answering my own post here, but not really satisfied with the
answer. If I create the trigger this way:
create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
part text;
q text;
BEGIN
part = to_char(new."timestamp",'YYYYMMDD');
q = 'insert into page_access_'||part||' values (
'||new.paid||',
'''||new.timestamp||''',
'||new.total_time||',
'''||new.http_host||''',
'''||new.php_self||''',
'''||new.query_string||''',
'''||new.remote_addr||''',
'''||new.logged_in||''',
'||new.uid||',
'''||new.http_user_agent||''',
'''||new.server_addr||''',
'''||new.notes||'''
)';
execute q;
-- insert into page_access_20090427 values (new.*);
return null;
END;
$$ language plpgsql;
It now works. I've tried a variety of constructs of new and || and '
and * and nothing easy like new.* seems to work.
Any suggestions greatly appreciated. Til then, the explicitly named
fields seems to work well enough.
On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > OK, I'm hitting a wall here. I've written this trigger for partitioning: > > create or replace function page_access_insert_trigger () > returns trigger as $$ > DECLARE > part text; > q text; > BEGIN > part = to_char(new."timestamp",'YYYYMMDD'); > q = 'insert into page_access_'||part||' values (new.*)'; > ... > > When I create it and try to use it I get this error: > ERROR: NEW used in query that is not in a rule > CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)" At this point I don't think that there is a way for this function to know the correct table type of new.* since page_access_... is still only a concatenated string. There there a way to cast new.* to the correct table type as part of this insert statement? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma <richard.broersma@gmail.com> wrote: > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> OK, I'm hitting a wall here. I've written this trigger for partitioning: >> >> create or replace function page_access_insert_trigger () >> returns trigger as $$ >> DECLARE >> part text; >> q text; >> BEGIN >> part = to_char(new."timestamp",'YYYYMMDD'); >> q = 'insert into page_access_'||part||' values (new.*)'; >> ... >> >> When I create it and try to use it I get this error: >> ERROR: NEW used in query that is not in a rule >> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)" > > At this point I don't think that there is a way for this function to > know the correct table type of new.* since page_access_... is still > only a concatenated string. There there a way to cast new.* to the > correct table type as part of this insert statement? I tried casting the new.*::page_access and that didn't work. For now I'll carry on with the complete listing of everything.
On Mon, Apr 27, 2009 at 03:37:22PM -0600, Scott Marlowe wrote: > On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma wrote: > > At this point I don't think that there is a way for this function to > > know the correct table type of new.* since page_access_... is still > > only a concatenated string. There there a way to cast new.* to the > > correct table type as part of this insert statement? > > I tried casting the new.*::page_access and that didn't work. For now > I'll carry on with the complete listing of everything. In SQL I can do: PREPARE _p(parent) AS INSERT INTO subtable SELECT ($1).*; EXECUTE _p(new); DEALLOCATE _p; however this seems to interact badly with the EXECUTE in plpgsql, not sure how to work around that. -- Sam http://samason.me.uk/
On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> OK, I'm hitting a wall here. I've written this trigger for partitioning:
>>
>> create or replace function page_access_insert_trigger ()
>> returns trigger as $$
>> DECLARE
>> part text;
>> q text;
>> BEGIN
>> part = to_char(new."timestamp",'YYYYMMDD');
>> q = 'insert into page_access_'||part||' values (new.*)';
>> ...
>>
>> When I create it and try to use it I get this error:
>> ERROR: NEW used in query that is not in a rule
>> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)"
>
> At this point I don't think that there is a way for this function to
> know the correct table type of new.* since page_access_... is still
> only a concatenated string. There there a way to cast new.* to the
> correct table type as part of this insert statement?
Oh man, it just gets worse. I really need a simple elegant solution
here, because if I try to build the query by hand null inputs make
life a nightmare. I had built something like this:
q = 'insert into '||schem||'.page_access_'||part||' values (
'||new.paid||',
'''||new.timestamp||''',
'||new.total_time||',
'''||new.http_host||''',
'''||new.php_self||''',
'''||new.query_string||''',
'''||new.remote_addr||''',
'''||new.logged_in||''',
'||new.uid||',
'''||new.http_user_agent||''',
'''||new.server_addr||''',
'''||new.notes||'''
)';
execute q;
But if any of the fields referenced are null, the whole query string
is now null. So the next step is to use coalesce to build a query
string? That get insane very quickly. There's got to be some quoting
trick or something to let me use new.*, please someone see this and
know what that trick is.
On 2009-04-29, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
><richard.broersma@gmail.com> wrote:
>> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>> OK, I'm hitting a wall here. I've written this trigger for partitioning:
>>>
>>> create or replace function page_access_insert_trigger ()
>>> returns trigger as $$
>>> DECLARE
>>> part text;
>>> q text;
>>> BEGIN
>>> part = to_char(new."timestamp",'YYYYMMDD');
>>> q = 'insert into page_access_'||part||' values (new.*)';
>>> ...
>>>
>>> When I create it and try to use it I get this error:
>>> ERROR: NEW used in query that is not in a rule
>>> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)"
>>
>> At this point I don't think that there is a way for this function to
>> know the correct table type of new.* since page_access_... is still
>> only a concatenated string. There there a way to cast new.* to the
>> correct table type as part of this insert statement?
>
> Oh man, it just gets worse. I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare. I had built something like this:
sounds like you want quote_literal() and/or coalesce()
EXECUTE 'insert into '|| quote_ident($1) || ' (data) values (' ||
coalesce(quote_literal( $2 ),'NULL');
On Apr 29, 2009, at 4:14 AM, Jasen Betts wrote:
> On 2009-04-29, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>> <richard.broersma@gmail.com> wrote:
>>> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com
>>> > wrote:
>>>> OK, I'm hitting a wall here. I've written this trigger for
>>>> partitioning:
>>>>
>>>> create or replace function page_access_insert_trigger ()
>>>> returns trigger as $$
>>>> DECLARE
>>>> part text;
>>>> q text;
>>>> BEGIN
>>>> part = to_char(new."timestamp",'YYYYMMDD');
>>>> q = 'insert into page_access_'||part||' values (new.*)';
>>>> ...
>>>>
>>>> When I create it and try to use it I get this error:
>>>> ERROR: NEW used in query that is not in a rule
>>>> CONTEXT: SQL statement "insert into page_access_20090427 values
>>>> (new.*)"
>>>
>>> At this point I don't think that there is a way for this function to
>>> know the correct table type of new.* since page_access_... is still
>>> only a concatenated string. There there a way to cast new.* to the
>>> correct table type as part of this insert statement?
>>
>> Oh man, it just gets worse. I really need a simple elegant solution
>> here, because if I try to build the query by hand null inputs make
>> life a nightmare. I had built something like this:
>
> sounds like you want quote_literal() and/or coalesce()
>
> EXECUTE 'insert into '|| quote_ident($1) || ' (data) values (' ||
> coalesce(quote_literal( $2 ),'NULL');
I'm fairly certain that quote_literal doesn't work with null values,
hence the inclusion of quote_nullable() in 8.4. I ran into this same
issue when working on a pet project called pg_partitioner (http://github.com/mage2k/pg_partitioner/tree/master
). Since 8.4 obviously wasn't available for that yet I ended writing
my own quote_nullable(), pretty simple.
Scott,
I also couldn't come up with a dynamic way to use new.* so I ended up
just writing out the attribute names in my partition triggers, as
well. In fact, you may want to take a look at pg_partitioner. It
needs some polish but most of the basic functionality is there.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
> OK, I'm hitting a wall here. I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
> part text;
> q text;
> BEGIN
> part = to_char(new."timestamp",'YYYYMMDD');
> q = 'insert into page_access_'||part||' values (new.*)';
What you want looks like this (thanks RhodiumToad):
'INSERT INTO page_access_' || part ||
'SELECT (' || quote_literal(textin(record_out(NEW))) || '::page_access).*;'
That's supposing you have a parent table named page_access, of course. And
casting this way has drawbacks too (which I can't recall at this moment), but
I've been using this live for maybe more than a year now without any problem.
> It works. So, how am I supposed to run it with dynamic table names?
Hack your way around, partitioning is not yet there "for real"...
--
dim
Вложения
On Tue, Apr 28, 2009 at 11:24 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Tue, Apr 28, 2009 at 10:46 PM, David Fetter <david@fetter.org> wrote: >> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote: >>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma >>> <richard.broersma@gmail.com> wrote: >>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >>> >> OK, I'm hitting a wall here. I've written this trigger for partitioning: >>> >> >>> >> create or replace function page_access_insert_trigger () >>> >> returns trigger as $$ >>> >> DECLARE >>> >> part text; >>> >> q text; >>> >> BEGIN >>> >> part = to_char(new."timestamp",'YYYYMMDD'); >>> >> q = 'insert into page_access_'||part||' values (new.*)'; >>> >> ... >>> >> >>> >> When I create it and try to use it I get this error: >>> >> ERROR: NEW used in query that is not in a rule >>> >> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)" >>> > >>> > At this point I don't think that there is a way for this function to >>> > know the correct table type of new.* since page_access_... is still >>> > only a concatenated string. There there a way to cast new.* to the >>> > correct table type as part of this insert statement? >>> >>> Oh man, it just gets worse. I really need a simple elegant solution >>> here, because if I try to build the query by hand null inputs make >>> life a nightmare. I had built something like this: >>> >>> q = 'insert into '||schem||'.page_access_'||part||' values ( >>> '||new.paid||', >>> '''||new.timestamp||''', >>> '||new.total_time||', >>> '''||new.http_host||''', >>> '''||new.php_self||''', >>> '''||new.query_string||''', >>> '''||new.remote_addr||''', >>> '''||new.logged_in||''', >>> '||new.uid||', >>> '''||new.http_user_agent||''', >>> '''||new.server_addr||''', >>> '''||new.notes||''' >>> )'; >>> execute q; >>> >>> But if any of the fields referenced are null, the whole query string >>> is now null. So the next step is to use coalesce to build a query >>> string? That get insane very quickly. There's got to be some >>> quoting trick or something to let me use new.*, please someone see >>> this and know what that trick is. >> >> Well, you can add in piles of COALESCE, but that way madness lies. >> >> Instead, use dollar quoting, the appropriate quote_*() functions, and >> this: >> >> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers > > Thanks so much! I'm off to read up on it. Dollar quoting, quote() > and the wiki. Thanks again. OK, I wrote a quick test and it's not working. I've tried a few combinations here and there but nothing seems to kick it off. create or replace function page_access_test () returns trigger as $$ DECLARE var text; BEGIN EXECUTE 'SELECT (' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').' || quote_ident(http_host) INTO var; raise notice '%',var; END; $$ language plpgsql; which generates the error: ERROR: column "http_host" does not exist I'm pretty sure that column exists in the table. Here's the line for \d on page_access: http_host | text I've tried new.http_host, which when http_host='xyz' generates an ERROR: type "public.xyz" does not exist It's late, I'll mess with this tomorrow. This is really frustrating me and I feel dirty if I resort to a cron job to create the new table. I've tested the basic time to do all the work on my laptop and the code runs pretty fast there. So checking to see if the table is there doesn't seem a particularly expensive select. It's on a small system table that stays cached. My laptop can run the main code loop with inserts (and lying fsync of course) 1500 times per second. Without the check it can run 1700 a second. We do a dozen a minute. So unless our application goes insane and starts inserting data a couple thousand times faster it's a non-issue. I want a simple, self sustaining solution that requires no cron jobs to work. If someone has a simple dynamic trigger example in any scripting language like plpgsql, plperl or pltcl please post it. I don't want to maintain C triggers for this on a production server. If I can't get it working I'll implement the cron job.
On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote: > On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma > <richard.broersma@gmail.com> wrote: > > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > >> OK, I'm hitting a wall here. I've written this trigger for partitioning: > >> > >> create or replace function page_access_insert_trigger () > >> returns trigger as $$ > >> DECLARE > >> part text; > >> q text; > >> BEGIN > >> part = to_char(new."timestamp",'YYYYMMDD'); > >> q = 'insert into page_access_'||part||' values (new.*)'; > >> ... > >> > >> When I create it and try to use it I get this error: > >> ERROR: NEW used in query that is not in a rule > >> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)" > > > > At this point I don't think that there is a way for this function to > > know the correct table type of new.* since page_access_... is still > > only a concatenated string. There there a way to cast new.* to the > > correct table type as part of this insert statement? > > Oh man, it just gets worse. I really need a simple elegant solution > here, because if I try to build the query by hand null inputs make > life a nightmare. I had built something like this: > > q = 'insert into '||schem||'.page_access_'||part||' values ( > '||new.paid||', > '''||new.timestamp||''', > '||new.total_time||', > '''||new.http_host||''', > '''||new.php_self||''', > '''||new.query_string||''', > '''||new.remote_addr||''', > '''||new.logged_in||''', > '||new.uid||', > '''||new.http_user_agent||''', > '''||new.server_addr||''', > '''||new.notes||''' > )'; > execute q; > > But if any of the fields referenced are null, the whole query string > is now null. So the next step is to use coalesce to build a query > string? That get insane very quickly. There's got to be some > quoting trick or something to let me use new.*, please someone see > this and know what that trick is. Well, you can add in piles of COALESCE, but that way madness lies. Instead, use dollar quoting, the appropriate quote_*() functions, and this: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Scott Marlowe escribió: > Oh man, it just gets worse. I really need a simple elegant solution > here, because if I try to build the query by hand null inputs make > life a nightmare. I had built something like this: > > q = 'insert into '||schem||'.page_access_'||part||' values ( > '||new.paid||', > '''||new.timestamp||''', > '||new.total_time||', > '''||new.http_host||''', > '''||new.php_self||''', > '''||new.query_string||''', > '''||new.remote_addr||''', > '''||new.logged_in||''', > '||new.uid||', > '''||new.http_user_agent||''', > '''||new.server_addr||''', > '''||new.notes||''' > )'; > execute q; > > But if any of the fields referenced are null, the whole query string > is now null. So the next step is to use coalesce to build a query > string? That get insane very quickly. There's got to be some quoting > trick or something to let me use new.*, please someone see this and > know what that trick is. Agreed, it is ugly. I don't think there's a better way to do it though. One thing you could try is getting the column names and types from the catalogs to build the insert statement. That way you don't have to list each column separately, and you don't need to fiddle with whether each value needs quotes or not. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Wed, Apr 29, 2009 at 4:23 AM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote: > >> Oh man, it just gets worse. I really need a simple elegant solution >> here, because if I try to build the query by hand null inputs make >> life a nightmare. I had built something like this: >> >> q = 'insert into '||schem||'.page_access_'||part||' values ( >> '||new.paid||', >> '''||new.timestamp||''', >> '||new.total_time||', >> '''||new.http_host||''', >> '''||new.php_self||''', >> '''||new.query_string||''', >> '''||new.remote_addr||''', >> '''||new.logged_in||''', >> '||new.uid||', >> '''||new.http_user_agent||''', >> '''||new.server_addr||''', >> '''||new.notes||''' >> )'; >> execute q; >> >> But if any of the fields referenced are null, the whole query string >> is now null. So the next step is to use coalesce to build a query >> string? That get insane very quickly. There's got to be some quoting >> trick or something to let me use new.*, please someone see this and >> know what that trick is. > > > I think you could do this if you'd be using a PL-language that supported > reflection (on the NEW objects' type in this case). I can't say I know which > one does though, I've only been using PL/pgsql so far, but I'd guess > PL/Python, PL/Perl or PL/Java should be able to do the trick. Or plain C. > > AFAIK there's no way to dynamically list column names from a table-type > variable like NEW in PL/pgsql, which is why the above probably can't be done > any easier using PL/pgsql. It would be nice to be able to LOOP over a > variable like that or some similar method (I guess a more relational > approach where the columns would be available as a result set would be > preferred), especially if it'd be similarly easy to inspect the name and > type of each column. I'm really close to using coalesce to make this work, since I can't get the referenced at http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers to work. If there was some simple quoting trick to get the original (NEW.*) stuff to work I'd be gold. Either that or just implement this all in rules with a simple cron job that creates the new table as needed a week or so in advnace.
I wonder if it would be easier to perodically replace the entire trigger function with one that inserts to the correct table using CRON+SED rather than dynamically building SQL. This might be a bad idea however. I'm just thinking outside the box. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Tue, Apr 28, 2009 at 10:46 PM, David Fetter <david@fetter.org> wrote: > On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote: >> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma >> <richard.broersma@gmail.com> wrote: >> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> >> OK, I'm hitting a wall here. I've written this trigger for partitioning: >> >> >> >> create or replace function page_access_insert_trigger () >> >> returns trigger as $$ >> >> DECLARE >> >> part text; >> >> q text; >> >> BEGIN >> >> part = to_char(new."timestamp",'YYYYMMDD'); >> >> q = 'insert into page_access_'||part||' values (new.*)'; >> >> ... >> >> >> >> When I create it and try to use it I get this error: >> >> ERROR: NEW used in query that is not in a rule >> >> CONTEXT: SQL statement "insert into page_access_20090427 values (new.*)" >> > >> > At this point I don't think that there is a way for this function to >> > know the correct table type of new.* since page_access_... is still >> > only a concatenated string. There there a way to cast new.* to the >> > correct table type as part of this insert statement? >> >> Oh man, it just gets worse. I really need a simple elegant solution >> here, because if I try to build the query by hand null inputs make >> life a nightmare. I had built something like this: >> >> q = 'insert into '||schem||'.page_access_'||part||' values ( >> '||new.paid||', >> '''||new.timestamp||''', >> '||new.total_time||', >> '''||new.http_host||''', >> '''||new.php_self||''', >> '''||new.query_string||''', >> '''||new.remote_addr||''', >> '''||new.logged_in||''', >> '||new.uid||', >> '''||new.http_user_agent||''', >> '''||new.server_addr||''', >> '''||new.notes||''' >> )'; >> execute q; >> >> But if any of the fields referenced are null, the whole query string >> is now null. So the next step is to use coalesce to build a query >> string? That get insane very quickly. There's got to be some >> quoting trick or something to let me use new.*, please someone see >> this and know what that trick is. > > Well, you can add in piles of COALESCE, but that way madness lies. > > Instead, use dollar quoting, the appropriate quote_*() functions, and > this: > > http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers Thanks so much! I'm off to read up on it. Dollar quoting, quote() and the wiki. Thanks again.
On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote: > Oh man, it just gets worse. I really need a simple elegant solution > here, because if I try to build the query by hand null inputs make > life a nightmare. I had built something like this: > > q = 'insert into '||schem||'.page_access_'||part||' values ( > '||new.paid||', > '''||new.timestamp||''', > '||new.total_time||', > '''||new.http_host||''', > '''||new.php_self||''', > '''||new.query_string||''', > '''||new.remote_addr||''', > '''||new.logged_in||''', > '||new.uid||', > '''||new.http_user_agent||''', > '''||new.server_addr||''', > '''||new.notes||''' > )'; > execute q; > > But if any of the fields referenced are null, the whole query string > is now null. So the next step is to use coalesce to build a query > string? That get insane very quickly. There's got to be some quoting > trick or something to let me use new.*, please someone see this and > know what that trick is. I think you could do this if you'd be using a PL-language that supported reflection (on the NEW objects' type in this case). I can't say I know which one does though, I've only been using PL/pgsql so far, but I'd guess PL/Python, PL/Perl or PL/Java should be able to do the trick. Or plain C. AFAIK there's no way to dynamically list column names from a table- type variable like NEW in PL/pgsql, which is why the above probably can't be done any easier using PL/pgsql. It would be nice to be able to LOOP over a variable like that or some similar method (I guess a more relational approach where the columns would be available as a result set would be preferred), especially if it'd be similarly easy to inspect the name and type of each column. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49f82a8c129742043099112!
Hi, it seems it didn't make it the first time.
Début du message réexpédié :
> De : Dimitri Fontaine <dfontaine@hi-media.com>
> Date : 30 avril 2009 12:03:10 HAEC
> À : pgsql-general@postgresql.org
> Objet : Rép : [GENERAL] triggers and execute...
>
> On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
>> OK, I'm hitting a wall here. I've written this trigger for
>> partitioning:
>>
>> create or replace function page_access_insert_trigger ()
>> returns trigger as $$
>> DECLARE
>> part text;
>> q text;
>> BEGIN
>> part = to_char(new."timestamp",'YYYYMMDD');
>> q = 'insert into page_access_'||part||' values (new.*)';
>
> What you want looks like this (thanks RhodiumToad):
>
> 'INSERT INTO page_access_' || part ||
> 'SELECT (' || quote_literal(textin(record_out(NEW))) ||
> '::page_access).*;'
>
> That's supposing you have a parent table named page_access, of
> course. And
> casting this way has drawbacks too (which I can't recall at this
> moment), but
> I've been using this live for maybe more than a year now without any
> problem.
>
>> It works. So, how am I supposed to run it with dynamic table names?
>
> Hack your way around, partitioning is not yet there "for real"...
--
dim