pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Converting T-SQL to PostgreSQL
3+ messages / 2 participants
[nested] [flat]

* Converting T-SQL to PostgreSQL
@ 2009-11-05 20:37  maboyz <thabani.moyo@distributel.ca>
  0 siblings, 1 reply; 3+ messages in thread

From: maboyz @ 2009-11-05 20:37 UTC (permalink / raw)
  To: pgsql-sql


I am trying to re write the following stored proc as a postgresql
function......but am new to postgres and it is kind of getting really
confusing trying to learn and let alone understand the syntax:

CREATE PROCEDURE [dbo].[GetAppAvailability]
	@AuditAvailable		bit output,
	@BillingAvailable	bit output,
	@ReturnValue		int output
AS
SET NOCOUNT ON

set @AuditAvailable = (select app_Status from AppAvailability where
app_Functionality = 'Audit')
set @BillingAvailable = (select app_Status from AppAvailability where
app_Functionality = 'Billing')

Set @ReturnValue = @@Error

I have this in postgres but obviously it is not correct:

CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character
varying, OUT billingAvailable character varying)
   AS
$BODY$
     set $1 = (select "app_Status" from "AppAvailability" where
"app_Functionality" = 'Audit');
    set $2 = (select "app_Status" from "AppAvailability" where
"app_Functionality" = 'Billing');
	$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;



-- 
View this message in context: http://old.nabble.com/Converting-T-SQL-to-PostgreSQL-tp26221691p26221691.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Converting T-SQL to PostgreSQL
@ 2009-11-05 20:53  Pavel Stehule <pavel.stehule@gmail.com>
  parent: maboyz <thabani.moyo@distributel.ca>
  0 siblings, 1 reply; 3+ messages in thread

From: Pavel Stehule @ 2009-11-05 20:53 UTC (permalink / raw)
  To: maboyz <thabani.moyo@distributel.ca>; +Cc: pgsql-sql

Hello

2009/11/5 maboyz <thabani.moyo@distributel.ca>:
>
> I am trying to re write the following stored proc as a postgresql
> function......but am new to postgres and it is kind of getting really
> confusing trying to learn and let alone understand the syntax:
>
> CREATE PROCEDURE [dbo].[GetAppAvailability]
>        @AuditAvailable         bit output,
>        @BillingAvailable       bit output,
>        @ReturnValue            int output
> AS
> SET NOCOUNT ON
>
> set @AuditAvailable = (select app_Status from AppAvailability where
> app_Functionality = 'Audit')
> set @BillingAvailable = (select app_Status from AppAvailability where
> app_Functionality = 'Billing')
>
> Set @ReturnValue = @@Error
>
> I have this in postgres but obviously it is not correct:
>
> CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character
> varying, OUT billingAvailable character varying)
>   AS
> $BODY$
>     set $1 = (select "app_Status" from "AppAvailability" where
> "app_Functionality" = 'Audit');
>    set $2 = (select "app_Status" from "AppAvailability" where
> "app_Functionality" = 'Billing');
>        $BODY$
>  LANGUAGE 'sql' VOLATILE
>  COST 100;
>

You can use SQL functions, but SQL has not assign statement. So you
have to use plpgsql language.
http://www.postgresql.org/docs/8.4/interactive/plpgsql.html

You code should be

CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character
 varying, OUT billingAvailable character varying)
   AS
 $BODY$
     $1 = (select "app_Status" from "AppAvailability" where
 "app_Functionality" = 'Audit');
    $2 = (select "app_Status" from "AppAvailability" where
 "app_Functionality" = 'Billing');
        $BODY$
  LANGUAGE plpgsql VOLATILE

Usually is better if you forgot on T-SQL and try to learn language again.

there are some good advices:

a) don't use camel notation for identifiers
b) don't use case sensitive identifiers like "some"
c) don't create "SELECT only" functions

Why do you use function?

use view:
CREATE VIEW GetAppAvailability AS
  SELECT (SELECT "app_Status"
                   FROM "AppAvailability"
                  WHERE "app_Functionality" = 'Audit'),
                (SELECT "app_Status"
                    FROM "AppAvailability"
                   WHERE "app_Functionality" = 'Billing');


some link
http://www.postgres.cz/index.php/PL/pgSQL_%28en%29

regards
Pavel Stehule
>
> --
> View this message in context: http://old.nabble.com/Converting-T-SQL-to-PostgreSQL-tp26221691p26221691.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Converting T-SQL to PostgreSQL
@ 2009-11-05 20:55  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Pavel Stehule @ 2009-11-05 20:55 UTC (permalink / raw)
  To: maboyz <thabani.moyo@distributel.ca>; +Cc: pgsql-sql

2009/11/5 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2009/11/5 maboyz <thabani.moyo@distributel.ca>:
>>
>> I am trying to re write the following stored proc as a postgresql
>> function......but am new to postgres and it is kind of getting really
>> confusing trying to learn and let alone understand the syntax:
>>
>> CREATE PROCEDURE [dbo].[GetAppAvailability]
>>        @AuditAvailable         bit output,
>>        @BillingAvailable       bit output,
>>        @ReturnValue            int output
>> AS
>> SET NOCOUNT ON
>>
>> set @AuditAvailable = (select app_Status from AppAvailability where
>> app_Functionality = 'Audit')
>> set @BillingAvailable = (select app_Status from AppAvailability where
>> app_Functionality = 'Billing')
>>
>> Set @ReturnValue = @@Error
>>
>> I have this in postgres but obviously it is not correct:
>>
>> CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character
>> varying, OUT billingAvailable character varying)
>>   AS
>> $BODY$
>>     set $1 = (select "app_Status" from "AppAvailability" where
>> "app_Functionality" = 'Audit');
>>    set $2 = (select "app_Status" from "AppAvailability" where
>> "app_Functionality" = 'Billing');
>>        $BODY$
>>  LANGUAGE 'sql' VOLATILE
>>  COST 100;
>>
>
> You can use SQL functions, but SQL has not assign statement. So you
> have to use plpgsql language.
> http://www.postgresql.org/docs/8.4/interactive/plpgsql.html
>
> You code should be
>
> CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character
>  varying, OUT billingAvailable character varying)
>   AS
>  $BODY$
BEGIN

>     $1 = (select "app_Status" from "AppAvailability" where
>  "app_Functionality" = 'Audit');
>    $2 = (select "app_Status" from "AppAvailability" where
>  "app_Functionality" = 'Billing');

END
>        $BODY$
>  LANGUAGE plpgsql VOLATILE
>

sorry, I left out main block. plpgsql function have to have block etc
BEGIN .... END;

> Usually is better if you forgot on T-SQL and try to learn language again.
>
> there are some good advices:
>
> a) don't use camel notation for identifiers
> b) don't use case sensitive identifiers like "some"
> c) don't create "SELECT only" functions
>
> Why do you use function?
>
> use view:
> CREATE VIEW GetAppAvailability AS
>  SELECT (SELECT "app_Status"
>                   FROM "AppAvailability"
>                  WHERE "app_Functionality" = 'Audit'),
>                (SELECT "app_Status"
>                    FROM "AppAvailability"
>                   WHERE "app_Functionality" = 'Billing');
>
>
> some link
> http://www.postgres.cz/index.php/PL/pgSQL_%28en%29
>
> regards
> Pavel Stehule
>>
>> --
>> View this message in context: http://old.nabble.com/Converting-T-SQL-to-PostgreSQL-tp26221691p26221691.html
>> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2009-11-05 20:55 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-05 20:37 Converting T-SQL to PostgreSQL maboyz <thabani.moyo@distributel.ca>
2009-11-05 20:53 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-05 20:55   ` Pavel Stehule <pavel.stehule@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox