Thread: Correct Insert SQL syntax?

<div dir="ltr">Will this syntax work:<br /><br />fcn_stats are all in the same schema<br /><br />CREATE OR REPLACE
FUNCTIONinsert_stats(p_date date)<br />RETURNS void AS $$<br />          <br />          BEGIN<br />                   
insertinto stats (<br />                             date,<br />                            stats1,<br
/>                           stats2<br />                    ) (select<br />                          p_date,<br
/>                         fcn_stats1(p_date,'basic'),<br />                           fcn_stats2(p_date,'basic',0)<br
/>                         from dual<br />                     );<br />END;<br />$$ LANGUAGE 'plpgsql';<br
/>                   <br /></div> 

There is no internal dual table in PG unlike Ora:) If you need to
invoke non-set-returning function simply execute:

SELECT my_func(p1, p2...);

-- 
Best regards, Yuri.

--- On Thu, 9/4/08, Ruben Gouveia <rubes7202@gmail.com> wrote:

> From: Ruben Gouveia <rubes7202@gmail.com>
> Subject: [SQL] Correct Insert SQL syntax?
> To: "pgsql-sql" <pgsql-sql@postgresql.org>
> Date: Thursday, September 4, 2008, 10:16 PM
> Will this syntax work:
> 
> fcn_stats are all in the same schema
> 
> CREATE OR REPLACE FUNCTION insert_stats(p_date date)
> RETURNS void AS $$
> 
>           BEGIN
>                     insert into stats (
>                             date,
>                             stats1,
>                             stats2
>                     ) (select
>                           p_date,
>                          
> fcn_stats1(p_date,'basic'),
>                          
> fcn_stats2(p_date,'basic',0)
>                           from dual
>                      );
> END;
> $$ LANGUAGE 'plpgsql';


Dual is a table create for you or is the generic table of oracle?


     

On Fri, Sep 5, 2008 at 10:11 AM, Lennin Caro <lennin.caro@yahoo.com> wrote:
> --- On Thu, 9/4/08, Ruben Gouveia <rubes7202@gmail.com> wrote:
>>                     ) (select
>>                           p_date,
>>
>> fcn_stats1(p_date,'basic'),
>>
>> fcn_stats2(p_date,'basic',0)
>>                           from dual
>
> Dual is a table create for you or is the generic table of oracle?

Dual is a special table oracle creates that always has one row and one
row only so you have a target for your from clause always.  PostgreSQL
has the syntactic weirdness that everything is a function that makes
some sql syntax hard to implement or get changed, oracle's weirdness
(well, one of many really) is the requirement of a target table.  the
spec would seem to side with oracle on this, but it is a pain the
butt.