From: Pavel Stehule <pavel.stehule@gmail.com>
To: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Converting T-SQL to PostgreSQL
Date: Thu, 5 Nov 2009 21:55:53 +0100
Message-ID: <162867790911051255v337a9bc7h8c8cba7577c84075@mail.gmail.com> (raw)
In-Reply-To: <162867790911051253q2d18ffebw45ec20c710257a60@mail.gmail.com>
References: <26221691.post@talk.nabble.com>
<162867790911051253q2d18ffebw45ec20c710257a60@mail.gmail.com>
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
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, thabani.moyo@distributel.ca
Subject: Re: Converting T-SQL to PostgreSQL
In-Reply-To: <162867790911051255v337a9bc7h8c8cba7577c84075@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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