Thread: MS SQL - PostgreSQL

Dear Mailing List member,

I have been using MS SQL for a few years and now I am trying to migrate to PostgreSQL.
Are there any statements that similir for :
   - if [not] exists (select 1 from sysdatabases where name=[db_name])
         drop database [db_name]

   - if [not] exists (select 1 from sysobjects where type = 'U' and  name = [table_name])
         drop table [table_name]

   - if exists (select 1 from sysindexes where name = [index_name] and indid > 0)
         drop index [table_name].[index_name]

   - If in MS SQL there is 'store procedured', what about in PostgreSQL ?
   - is there any tools, like bcp (bulk copy) in postgresql ?

It is better to send plain text messages instead of html.

It is a pain to respond to emails in HTML format.

________________________________________
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Irfan Syukur
Sent: Tuesday, September 13, 2005 6:31 PM
To: POSTGRESQL (E-mail)
Subject: [GENERAL] MS SQL - PostgreSQL

Dear Mailing List member,
I have been using MS SQL for a few years and now I am trying to migrate to PostgreSQL.
Are there any statements that similir for :
   - if [not] exists (select 1 from sysdatabases where name=[db_name])
         drop database [db_name]
DC >>
Look at pg_database
<< DC
   - if [not] exists (select 1 from sysobjects where type = 'U' and  name = [table_name])
         drop table [table_name]
DC >>
Look at pg_tables
<< DC
   - if exists (select 1 from sysindexes where name = [index_name] and indid > 0)
         drop index [table_name].[index_name]
DC >>
Look at pg_indexes
<< DC
   - If in MS SQL there is 'store procedured', what about in PostgreSQL ?
DC >>
Sure.
<< DC

   - is there any tools, like bcp (bulk copy) in postgresql ?
>>
Look at the copy command.

There are some real nice PostgreSQL manuals online.  Try this:
http://www.postgresql.org/docs/8.0/static/index.html

it has a search function too.
<<

________________________________________

-------------------------------------------------------------------------------------------------------------------------
"Transmisi ini mungkin berisi informasi yang bersifat pribadi, rahasia dan tertutup untuk
dipublikasikan berdasarkan Hukum dan Peraturan Perundang-undangan yang berlaku di
wilayah Republik Indonesia. Jika Anda bukanlah penerima yang dituju, bersama ini Anda
diperingatkan bahwa semua publikasi, penggandaan, pendistribusian, atau penggunaan
informasi yang ada disini (berikut semua informasi yang terkait) adalah SANGAT
TERLARANG. Jika Anda menerima transmisi ini tanpa disengaja, harap segera hubungi
pengirim dan hapus material ini seluruhnya, baik dalam bentuk elektronik maupun
dokumen cetak. Terima kasih."

-------------------------------------------------------------------------------------------------------------------------

http://www.postgresql.org/docs/8.0/interactive/sql-createfunction.html

> -----Original Message-----
> From: Irfan Syukur [mailto:irfan_syukur@bri.co.id]
> Sent: Tuesday, September 13, 2005 8:37 PM
> To: Dann Corbit
> Subject: RE: [GENERAL] MS SQL - PostgreSQL
>
> Dear Dann,
>
> Thanks for your answer.
>
> Can PostGreSQL handle dynamic 'stored procedure'
> Can you give an example of writing 'stored procedure' in PostgreSQL ??
>
>
> -----Original Message-----
> From: Dann Corbit [mailto:DCorbit@connx.com]
> Sent: Wednesday, September 14, 2005 8:47 AM
> To: Irfan Syukur
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] MS SQL - PostgreSQL
>
>
> It is better to send plain text messages instead of html.
>
> It is a pain to respond to emails in HTML format.
>
> ________________________________________
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Irfan Syukur
> Sent: Tuesday, September 13, 2005 6:31 PM
> To: POSTGRESQL (E-mail)
> Subject: [GENERAL] MS SQL - PostgreSQL
>
> Dear Mailing List member,
> I have been using MS SQL for a few years and now I am trying to migrate to
> PostgreSQL.
> Are there any statements that similir for :
>    - if [not] exists (select 1 from sysdatabases where name=[db_name])
>          drop database [db_name]
> DC >>
> Look at pg_database
> << DC
>    - if [not] exists (select 1 from sysobjects where type = 'U' and  name
> = [table_name])
>          drop table [table_name]
> DC >>
> Look at pg_tables
> << DC
>    - if exists (select 1 from sysindexes where name = [index_name] and
> indid > 0)
>          drop index [table_name].[index_name]
> DC >>
> Look at pg_indexes
> << DC
>    - If in MS SQL there is 'store procedured', what about in PostgreSQL ?
> DC >>
> Sure.
> << DC
>
>    - is there any tools, like bcp (bulk copy) in postgresql ?
> >>
> Look at the copy command.
>
> There are some real nice PostgreSQL manuals online.  Try this:
> http://www.postgresql.org/docs/8.0/static/index.html
>
> it has a search function too.
> <<
>
> ________________________________________
> --------------------------------------------------------------------------
> -----------------------------------------------
> "Transmisi ini mungkin berisi informasi yang bersifat pribadi, rahasia dan
> tertutup untuk
> dipublikasikan berdasarkan Hukum dan Peraturan Perundang-undangan yang
> berlaku di
> wilayah Republik Indonesia. Jika Anda bukanlah penerima yang dituju,
> bersama ini Anda
> diperingatkan bahwa semua publikasi, penggandaan, pendistribusian, atau
> penggunaan
> informasi yang ada disini (berikut semua informasi yang terkait) adalah
> SANGAT
> TERLARANG. Jika Anda menerima transmisi ini tanpa disengaja, harap segera
> hubungi
> pengirim dan hapus material ini seluruhnya, baik dalam bentuk elektronik
> maupun
> dokumen cetak. Terima kasih."
> --------------------------------------------------------------------------
> -----------------------------------------------
>
>
> --------------------------------------------------------------------------
> -----------------------------------------------
> "Transmisi ini mungkin berisi informasi yang bersifat pribadi, rahasia dan
> tertutup untuk
> dipublikasikan berdasarkan Hukum dan Peraturan Perundang-undangan yang
> berlaku di
> wilayah Republik Indonesia. Jika Anda bukanlah penerima yang dituju,
> bersama ini Anda
> diperingatkan bahwa semua publikasi, penggandaan, pendistribusian, atau
> penggunaan
> informasi yang ada disini (berikut semua informasi yang terkait) adalah
> SANGAT
> TERLARANG. Jika Anda menerima transmisi ini tanpa disengaja, harap segera
> hubungi
> pengirim dan hapus material ini seluruhnya, baik dalam bentuk elektronik
> maupun
> dokumen cetak. Terima kasih."
> --------------------------------------------------------------------------
> -----------------------------------------------

Am Mittwoch, den 14.09.2005, 13:02 +0700 schrieb Irfan Syukur:
> Dear Tino,
>
> Thanks for your answer.
>
> Can PostGreSQL handle dynamic 'stored procedure'

whatever that means? :)
Postgres stored functions are not fully equivalent to
MSSQL stored procedures. Instead you use them just like
regular database functions or even tables.

> Can you give an example of writing 'stored procedure' in PostgreSQL ??

Did you have a look into the documentation?

http://www.postgresql.org/docs/8.0/static/

and specifically:

http://www.postgresql.org/docs/8.0/static/xplang.html

try it - the postgres documentation is really good!

btw, this would be worth reading (not only for you ;))
http://www.netmeister.org/news/learn2quote.html

HTH
Tino Wildenhain

PS: your mail server has a problem by now. I got an
error.

Am Donnerstag, den 15.09.2005, 07:43 +0700 schrieb Irfan Syukur:
> Dear Tino,
>
> In MS SQL, I can execute store procedure that I do not know it's name yet.
>
> A Simplicity example :
>    CREATE   procedure  dbo.sp_run_batch (@as_spname varchar(20)) with recompile as
>
>    declare @li_retstat  smallint,
>         @li_status   numeric(1,0), @ls_mesg   varchar(60)
>
>    exec @li_retstat = @as_spname @as_mesg = @ls_mesg output --(@as_spname = the name of stored procedure)
>    select @li_retstat, @ls_mesg
>    return
>    GO
>
> Can Postgres do that, how ?

Well, not that I know of. But in your example your function has a name.
So you simply:

SELECT dbo.sp_run_batch('something for spname');

I'd suggest you just play with it a bit - with an eye at the
docs.

>
> in MS SQL, there are datetime and timestamp data type, what data type should I use in Postgres

date, timestamp, timestamptz, ... whatever suits your application :)

> btw, this would be worth reading (not only for you ;))
> http://www.netmeister.org/news/learn2quote.html
>

Again ;)

HTH
Tino

Am Donnerstag, den 15.09.2005, 07:43 +0700 schrieb Irfan Syukur:


my Postfix reports:

 <irfan_syukur@bri.co.id>: mail for bri.co.id loops back to myself

this is weird. Your mail is apparently seriously broken. Please
have that fixed.

AntiSpam UOL schrieb:
>    ANTISPAM UOL » TIRA-TEIMA <http://antispam.uol.com.br>
>
> Olá,
>
> Você enviou uma mensagem para *rabuh@uol.com.br*
> Para que sua mensagem seja encaminhada, por favor, *clique aqui*


*snip*


>
> Esta confirmação é necessária porque *rabuh@uol.com.br* usa o Antispam
> UOL, um programa que elimina mensagens enviadas por robôs, como
> pornografia, propaganda e correntes.
>
> *As próximas mensagens enviadas para rabuh@uol.com.br não precisarão ser
> confirmadas*.*
> *Caso você receba outro pedido de confirmação, por favor, peça para
> rabuh@uol.com.br incluí-lo em sua lista de autorizados.
>
> *Atenção!* Se você não conseguir clicar no atalho acima, acesse este
> endereço:

*snip*

> ------------------------------------------------------------------------
>
> Hi,
>
> You´ve just sent a message to *rabuh@uol.com.br*
> In order to confirm the sent message, please *click here*

*snip*
>
> This confirmation is necessary because *rabuh@uol.com.br* uses Antispam
> UOL, a service that avoids unwanted messages like advertising,
> pornography, viruses, and spams.
>
> *Other messages sent to rabuh@uol.com.br won't need to be confirmed*.*
> *If you receive another confirmation request, please ask
> rabuh@uol.com.br to include you in his/her authorized e-mail list.
>
> *Warning!* If the link doesn´t work, please copy the address below and
> paste it on your browser:

*snip very long url*

>
>
> Use o *AntiSpam UOL* <http://antispam.uol.com.br> e proteja sua caixa postal
>

I say no! Not on mailinglists. Please people whoever you are, if you
post to mailinglists, accept the replys from there.

On Thu, 15 Sep 2005 07:16:25 +0200, Tino Wildenhain <tino@wildenhain.de> wrote:
> Am Donnerstag, den 15.09.2005, 07:43 +0700 schrieb Irfan Syukur:
> > Dear Tino,
> >
> > In MS SQL, I can execute store procedure that I do not know it's name yet.
> >
> > A Simplicity example :
> >    CREATE   procedure  dbo.sp_run_batch (@as_spname varchar(20)) with recompile as
> >
> >    declare @li_retstat  smallint,
> >         @li_status   numeric(1,0), @ls_mesg   varchar(60)
> >
> >    exec @li_retstat = @as_spname @as_mesg = @ls_mesg output --(@as_spname = the name of stored procedure)
> >    select @li_retstat, @ls_mesg
> >    return
> >    GO
> >
> > Can Postgres do that, how ?
>
> Well, not that I know of. But in your example your function has a name.


Essentially, it's a generic execute procedure.  Pass it a procedure name
and it runs it.  It's just "execute" from plpgsql.

http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+