Postgres Pro
Downloads
Home   >   mailing lists

Thread: dynamic insert in plpgsql

dynamic insert in plpgsql

From
Grzegorz Jaśkiewicz
Date:
Is there any nice way to do something like that in plpgsql:

  EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';

It would probably work, but some values are NULL, and plpgsql
interpreter just puts empty space there. So I get ('1',2,3,,,); Which
obviously is confusing INSERT.

thx.

--
GJ

Re: dynamic insert in plpgsql

From
Sam Mason
Date:
On Fri, Jan 08, 2010 at 02:55:53PM +0000, Grzegorz Jaaakiewicz wrote:
> Is there any nice way to do something like that in plpgsql:
>
>   EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
>
> It would probably work, but some values are NULL, and plpgsql
> interpreter just puts empty space there. So I get ('1',2,3,,,); Which
> obviously is confusing INSERT.

Yup, this thing is a bit fiddly.  Try:

  http://archives.postgresql.org/pgsql-general/2009-09/msg01176.php

--
  Sam  http://samason.me.uk/

Re: dynamic insert in plpgsql

From
Grzegorz Jaśkiewicz
Date:
On Fri, Jan 8, 2010 at 2:58 PM, Sam Mason <sam@samason.me.uk> wrote:

> Yup, this thing is a bit fiddly.  Try:
>
>  http://archives.postgresql.org/pgsql-general/2009-09/msg01176.php

I searched for it, but didn't stumble upon that one. Thanks.



--
GJ

Re: dynamic insert in plpgsql

From
Grzegorz Jaśkiewicz
Date:
what is that "(t" in the SELECT there for ?
or is it just typo, or something missing/etc ?

Re: dynamic insert in plpgsql

From
Grzegorz Jaśkiewicz
Date:
2010/1/8 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> what is that "(t" in the SELECT there for ?
> or is it just typo, or something missing/etc ?
>

ignore it. That's cast, for type t (table).



--
GJ

Re: dynamic insert in plpgsql

From
Dimitri Fontaine
Date:
Grzegorz Jaśkiewicz <gryzman@gmail.com> writes:

> Is there any nice way to do something like that in plpgsql:
>
>   EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';

See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
--
dim

Re: dynamic insert in plpgsql

From
Merlin Moncure
Date:
2010/1/9 Dimitri Fontaine <dfontaine@hi-media.com>:
> Grzegorz Jaśkiewicz <gryzman@gmail.com> writes:
>
>> Is there any nice way to do something like that in plpgsql:
>>
>>   EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
>
> See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
> --

that approach is awful.  Sam's method (or the one I posted in the same
thread using dollar quoting) is much faster/better.

merlin

Re: dynamic insert in plpgsql

From
Grzegorz Jaśkiewicz
Date:
This is what I hacked quickly last night, what you guys think?

CREATE OR REPLACE FUNCTION something.ziew_partition_insert() RETURNS TRIGGER AS
$_$
DECLARE
  partition_table_name varchar;
  old_partition_table_name varchar;
BEGIN

  SELECT 'something_partitions.ziew_'||to_char(NEW.logtime, 'IYYY_MM')
INTO partition_table_name;

  BEGIN

   EXECUTE 'INSERT INTO '||partition_table_name||'  (SELECT
(something.ziew '||quote_literal(NEW)||').*)';

    EXCEPTION
      WHEN undefined_table THEN
        BEGIN
          SET client_min_messages = error;

          EXECUTE 'CREATE TABLE '||partition_table_name||'() INHERITS
(something.ziew)';
          EXECUTE 'ALTER TABLE  '||partition_table_name||' ADD PRIMARY
KEY (id)';
          EXECUTE 'CREATE INDEX something_time'|| to_char(NEW.logtime,
'IYYY_MM')||' ON '||partition_table_name||'(logtime)';

          SELECT 'something_partitions.ziew_'||to_char(NEW.logtime-'2
months'::interval, 'IYYY_MM') INTO old_partition_table_name;
          -- don't care if it fails
          BEGIN
            EXECUTE 'DROP TABLE '||old_partition_table_name;
            EXCEPTION
              WHEN others THEN
                --- in place for NOP
                old_partition_table_name := '';
          END;

          EXECUTE 'INSERT INTO '||partition_table_name||'  (SELECT
(something.ziew '||quote_literal(NEW)||').*)';
        EXCEPTION
          WHEN others THEN
            RAISE EXCEPTION 'somethings wrong %',SQLERRM;
            RETURN NULL;
        END;
    END;

  RETURN NULL;
END;
$_$ LANGUAGE 'plpgsql';