Thread: Converting T-SQL to PostgreSQL
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; -- 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.
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 charactervarying, 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 >
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 >> >