From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | maboyz <thabani(dot)moyo(at)distributel(dot)ca> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Converting T-SQL to PostgreSQL |
Date: | 2009-11-05 20:53:54 |
Message-ID: | 162867790911051253q2d18ffebw45ec20c710257a60@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello
2009/11/5 maboyz <thabani(dot)moyo(at)distributel(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2009-11-05 20:55:53 | Re: Converting T-SQL to PostgreSQL |
Previous Message | maboyz | 2009-11-05 20:37:56 | Converting T-SQL to PostgreSQL |