Hello 2009/11/5 maboyz <thabani.m...@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$ $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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
