[SQL] 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, @ReturnValueint 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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Converting T-SQL to PostgreSQL
Hello 2009/11/5 maboyz : > > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Converting T-SQL to PostgreSQL
2009/11/5 Pavel Stehule : > Hello > > 2009/11/5 maboyz : >> >> 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 ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Foreign key columns
Hi. Is there a simple way to get foreign key data... for example I found a view, that does what I want ... It delivers fk_table | fk_column | pk_table | pk_column | constraint_name --++---+---+-- organisation | customer_rep | person| id| organisation_customer_rep_fkey organisation | ekstra_skema | ekstra_skema | id| org_schema_fkey organisation | in_group | organisation | id| organisation_in_group_fkey organisation | org_paying_company | organisation | id| organisation_org_paying_company_fkey organisation | primary_contact| person| id| primary_contact_fkey organisation | type | organisation_type | id| organisation_type_fkey The query in question is SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column, PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column, C.CONSTRAINT_NAME as Constraint_Name FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4; The only problem is that this query is sloow, runs in tens of seconds... Is there a good native (i.e. fast) pgsql-query to find that type of information? Svenne -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Foreign key columns
On Thu, Nov 5, 2009 at 2:08 PM, Svenne Krap wrote: > Hi. > > Is there a simple way to get foreign key data... for example I found a > view, that does what I want ... > > It delivers > > fk_table | fk_column | pk_table | pk_column | > constraint_name > > --++---+---+-- > > organisation | customer_rep | person | id | > organisation_customer_rep_fkey > > organisation | ekstra_skema | ekstra_skema | id | > org_schema_fkey > > organisation | in_group | organisation | id | > organisation_in_group_fkey > > organisation | org_paying_company | organisation | id | > organisation_org_paying_company_fkey > > organisation | primary_contact | person | id | > primary_contact_fkey > > organisation | type | organisation_type | id | > organisation_type_fkey > > > > The query in question is > > SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column, > PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column, > C.CONSTRAINT_NAME as Constraint_Name > FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C > INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME > = FK.CONSTRAINT_NAME > INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON > C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME > INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = > CU.CONSTRAINT_NAME > INNER JOIN ( > SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM > INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 > INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON > i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME > WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = > PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4; > > > The only problem is that this query is sloow, runs in tens of seconds... It runs in 112 milliseconds on my machine. Maybe your catalogs are extremely bloated? > Is there a good native (i.e. fast) pgsql-query to find that type of > information? This one seems to work pretty well. If you want to see a query to find such things, the easy way is to start psql with the -E switch, and issue a \d command on the organisation table and steal the SQL from there. That query will be pgsql specific, and possibly / likely pgsql VERSION dependent, so know that going into it. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
