Re: [SQL] Partitionning + Trigger and Execute not working as expected
On 2011-11-08, Sylvain Mougenot wrote:
> --f46d043c7fbad4a6b104b1357041
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
> I'm trying to use table partitionning on a table called JOB.
> Each month a new table is created to contain the rows created on that month=
> .
> ex : JOB_2011_11 for rows created during november 2011.
>
> To do that I followed this advices on that page :
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
> I also would like to create code dynamically into the trigger in order to
> have all "INSERT INTO" inheritated tables (tables like JOB__MM) queries
> done.
>
> But I can't make it work. I've an error when the insert is done using
> EXECUTE.
> *Working :* INSERT INTO job_2011_11 values (NEW.*);
> *Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || ' values
> (NEW.*)';
>
> Could someone tell me how to make this EXECUTE work?
EXECUTE 'INSERT INTO '|| currentTableName || ' select
('||quote_literal(NEW)||'::job%ROWTYPE).*';
or
EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updatable view should truncate table fields
On 2011-11-08, Russell Keane wrote: > > We can extend the table to accept more than 5 characters but the view must = > return 5 characters. > If we try to extend the table to accept, say, 10 characters the view will d= > isplay 10. > If I also cast the view field to 5 characters then any insert with more tha= > n 5 characters still fails. > > Any ideas??? re-load the view and functions, they are sill defined with the char(5) column (you'll probably yneed to drop them all (but not the table) first). plpgsql functions are partially compiled at the time they are defined subsequent modifictions to the datatypes in their definition will cause errors until they are re-defined -- -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
