Thread: Correct Insert SQL syntax?

Correct Insert SQL syntax?

From
"Ruben Gouveia"
Date:
<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> 

Re: Correct Insert SQL syntax?

From
"Yura Gal"
Date:
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.


Re: Correct Insert SQL syntax?

From
"Ruben Gouveia"
Date:
Thanks Yuri!

On Thu, Sep 4, 2008 at 3:49 PM, Yura Gal <yuragal@gmail.com> wrote:
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.

Re: Correct Insert SQL syntax?

From
"Bart Degryse"
Date:
You might wanna check out the PostgreSQL manual.
There's definitely an answer in it to all your questions.
Especially chapter 38 on migrating from Oracle to PostgreSQL might be helpful.
http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html

>>> "Ruben Gouveia" <rubes7202@gmail.com> 2008-09-05 0:56 >>>
Thanks Yuri!

On Thu, Sep 4, 2008 at 3:49 PM, Yura Gal <yuragal@gmail.com> wrote:
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.

Re: Correct Insert SQL syntax?

From
Lennin Caro
Date:


--- 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?


     



Re: Correct Insert SQL syntax?

From
"Scott Marlowe"
Date:
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.