DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=rYV1YUGhLENLaODkGLd1sxYXkwTDQuMHQXv1OiCyBGPAeun+3jWdaNMsmVB/6HLxE7
	QTTLHI5vgyG7Q6j3RNkUTu3WSo/nc+GwPFi2v7r8otGMQ5YzP1GVfccYbgbvizmllsDr
	R+tdnZsujxEYVaZPGJoQxGjQQYMJJw4FbSnOg=
MIME-Version: 1.0
In-Reply-To: <162867790911051253q2d18ffebw45ec20c710257a60@mail.gmail.com>
References: <26221691.post@talk.nabble.com>
	<162867790911051253q2d18ffebw45ec20c710257a60@mail.gmail.com>
Date: Thu, 5 Nov 2009 21:55:53 +0100
Message-ID: <162867790911051255v337a9bc7h8c8cba7577c84075@mail.gmail.com>
Subject: Re: Converting T-SQL to PostgreSQL
From: Pavel Stehule <pavel.stehule@gmail.com>
To: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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]
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0@AuditAvailable =C2=A0 =C2=A0 =C2=A0 =C2=A0 b=
it output,
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0@BillingAvailable =C2=A0 =C2=A0 =C2=A0 bit ou=
tput,
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0@ReturnValue =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0int output
>> AS
>> SET NOCOUNT ON
>>
>> set @AuditAvailable =3D (select app_Status from AppAvailability where
>> app_Functionality =3D 'Audit')
>> set @BillingAvailable =3D (select app_Status from AppAvailability where
>> app_Functionality =3D 'Billing')
>>
>> Set @ReturnValue =3D @@Error
>>
>> I have this in postgres but obviously it is not correct:
>>
>> CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable charact=
er
>> varying, OUT billingAvailable character varying)
>> =C2=A0 AS
>> $BODY$
>> =C2=A0 =C2=A0 set $1 =3D (select "app_Status" from "AppAvailability" whe=
re
>> "app_Functionality" =3D 'Audit');
>> =C2=A0 =C2=A0set $2 =3D (select "app_Status" from "AppAvailability" wher=
e
>> "app_Functionality" =3D 'Billing');
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0$BODY$
>> =C2=A0LANGUAGE 'sql' VOLATILE
>> =C2=A0COST 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 characte=
r
> =C2=A0varying, OUT billingAvailable character varying)
> =C2=A0 AS
> =C2=A0$BODY$
BEGIN

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

END
> =C2=A0 =C2=A0 =C2=A0 =C2=A0$BODY$
> =C2=A0LANGUAGE 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
> =C2=A0SELECT (SELECT "app_Status"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM "AppA=
vailability"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE "app_=
Functionality" =3D 'Audit'),
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(SELECT "app_Statu=
s"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM=
 "AppAvailability"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE "app=
_Functionality" =3D '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
>>
>