Re: Converting T-SQL to PostgreSQL - Mailing list pgsql-sql
From | Pavel Stehule |
---|---|
Subject | Re: Converting T-SQL to PostgreSQL |
Date | |
Msg-id | 162867790911051255v337a9bc7h8c8cba7577c84075@mail.gmail.com Whole thread Raw |
In response to | Re: Converting T-SQL to PostgreSQL (Pavel Stehule <pavel.stehule@gmail.com>) |
List | 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 >> >