[SQL] Partitioned Tables
I'm using partitioned tables where a child table is chosen based on a time stamp. One child table exists for each month. I move records from a temp table to the partitioned table via something like this: INSERT INTO parent_table SELECT * FROM temp_table; All works well but with when inserting to standard tables the insert result contains the number of records inserted. I've found that when I insert into partitioned tables the result is always zero. I'm using the TCL binding (pgintcl) but psql behaves the same way. Is there any way to determine the number of rows actually inserted? Thanks, Wayne -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to use schema with data type
I have tried to use a user defined data type I created within a schema. But I could not figure it out. CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF xchromasun.weekly_mpr AS CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF "xchromasun.weekly_mpr" AS I had to move the user defined data type to "public". Could someone explain how I might get that done - that is use a schema data type from a function. I'm using 8.4.x. Johnf -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Partitioned Tables
On 06/13/2012 08:05 AM, Wayne Cuddy wrote: I'm using partitioned tables where a child table is chosen based on a time stamp. One child table exists for each month. I move records from a temp table to the partitioned table via something like this: INSERT INTO parent_table SELECT * FROM temp_table; All works well but with when inserting to standard tables the insert result contains the number of records inserted. I've found that when I insert into partitioned tables the result is always zero. Are you inserting directly into the partition? Or into the parent table? If you're inserting into the parent, then it's true that no rows were inserted into the parent; a trigger redirected the write into the child table. Pg's handling of partitioning isn't yet sophisticated enough to recognise that those rows got written to a child and thus still appeared in the parent and report that. As a workaround, ignore the insert count for partitioned tables, or insert directly into the appropriate partition(s). -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to use schema with data type
On 06/13/2012 08:46 AM, John Fabiani wrote: I have tried to use a user defined data type I created within a schema. But I could not figure it out. CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF xchromasun.weekly_mpr AS CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF "xchromasun.weekly_mpr" AS I had to move the user defined data type to "public". Could someone explain how I might get that done - that is use a schema data type from a function. What exactly couldn't you work out? How was it going wrong, and when? What error messges did you get? The first form looks reasonable to me, though I haven't tested. If you need to quote the schema for caps reasons, you'd use: "xchromasun"."weekly_mpr" -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
