Re: [SQL] Execute format Insert sql failing with query stringargument of EXECUTE is null for column with timestamp with timezone - Mailing list pgsql-sql

I am a Postgres Newbie and looking for some help on how I can achieve to have null in child table column with "timestamp with time zone" when using EXECUTE format insert sql. I have a range partitioned table by day and want to insert data into the current day's partition. Trigger is available on the master table based on which the data would be directed to current day's partition. But I want to directly insert data into the child table rather than using trigger to have it done. We have a table which maintains the child table names along with its low and high date values. Below is kind of what I am doing --

CREATE OR REPLACE FUNCTION test_tab_func (BIGINT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE)
RETURNS boolean
AS 
$BODY$
DECLARE _id ALIAS FOR $1; _time ALIAS for $2; _parttime ALIAS for $3; _partition text; _z RECORD;
BEGIN
 SELECT child_table_name into STRICT _partition from child_tables where parent_table_name='test_tab' and part_low=_parttime;
 FOR _z IN SELECT table_id,                  analyzed_on             FROM test_tab            WHERE schemaname = 'app' LOOP      EXECUTE format('INSERT INTO dev.%s (id,                                           time,                                           table_id,                                           analyzed_on)                                            VALUES ('||_id|||',                                                    '''||_time ||''',                                                    '||_z.table_id||',                                                    '||_z.analyzed_on||')',_partition); END LOOP; RETURN true;
END;
$BODY$  LANGUAGE plpgsql;

The table "test_tab" has few rows with analyzed_on as null. When I run the function I get the below error ERROR: query string argument of EXECUTE is null. I way which I know is using COALESCE. But how do I achieve null value in my child table using COALESCE. I am not able to figure it out.

       EXECUTE format('INSERT INTO dev.%s (id,                                           time,                                           table_id,                                           analyzed_on)                                            VALUES ($1, $2, $3, $4)',_partition)

         USING _id, _time, _z.table_id, _z.analyzed_on;

---

View this message in context: Execute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.