Re: Indicating DEFAULT values in INSERT statement

[Postgres User <postgres.developer@xxxxxxxxx>]

Good idea but you're right, if the default value is an _expression_ such as a the next serial value, it will need to be executed first.  If Execute doesn't return the interpreted value, I don't know of a way to make it work..

On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina <diegoaugustomolina@xxxxxxxxx> wrote:

This is a bit hacky, but it may work (I haven't tried it yet). If it
works let us know and if it doesn't then maybe we can debug it and get
something useful, or move on to another solution.

<code>

 INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
       Case When p_created_ts Is Not Null Then p_created_ts Else

       ( -- You may want to be sure the field has a default value.
               SELECT d.adsrc -- or should it be d.adbin?
                       FROM
                               pg_catalog.pg_attribute a INNER JOIN
                               pg_catalog.pg_attrdef d ON (a.attnum = d.adnum)
                       WHERE
                               a.attname = 'created_ts' AND
                               a.attrelid = 'public.test'::REGCLASS AND
                               d.adrelid = 'public.test'::REGCLASS
       ) End
);
</code>

Well (thinking it thoroughly) it won't work at all as is. It will just
put the source code of the default _expression_ but you would need to
*interpret* it first.
Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry.

--

Diego Augusto Molina
diegoaugustomolina@xxxxxxxxx

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html