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=d89DeDshlvF+iHMyc5Dr5rnTMuyJUrZF0u7+5qMEnCMWBLacG0u8UAt5P1lO9Z2az2
	H6eY+pty8Sp8IXMgwM9XxPvivU3nTYYLkGwELBsntKfoeyy0PAYeaKWUZARhmsMUQjIe
	kANj1lIR37vn3/FiG5KPlanV3rzfeApDUuQgM=
MIME-Version: 1.0
In-Reply-To: <26221691.post@talk.nabble.com>
References: <26221691.post@talk.nabble.com>
Date: Thu, 5 Nov 2009 21:53:54 +0100
Message-ID: <162867790911051253q2d18ffebw45ec20c710257a60@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

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 bi=
t output,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0@BillingAvailable =C2=A0 =C2=A0 =C2=A0 bit out=
put,
> =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 characte=
r
> varying, OUT billingAvailable character varying)
> =C2=A0 AS
> $BODY$
> =C2=A0 =C2=A0 set $1 =3D (select "app_Status" from "AppAvailability" wher=
e
> "app_Functionality" =3D 'Audit');
> =C2=A0 =C2=A0set $2 =3D (select "app_Status" from "AppAvailability" where
> "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 character
 varying, OUT billingAvailable character varying)
   AS
 $BODY$
     $1 =3D (select "app_Status" from "AppAvailability" where
 "app_Functionality" =3D 'Audit');
    $2 =3D (select "app_Status" from "AppAvailability" where
 "app_Functionality" =3D '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" =3D 'Audit'),
                (SELECT "app_Status"
                    FROM "AppAvailability"
                   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-P=
ostgreSQL-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
>