renesisx
asked on
How to use CASE and EXISTS?
PostgreSQL question...
I want something like this:
CASE WHEN EXISTS(SELECT constraint_name FROM information_schema.table_c onstraints WHERE constraint_name = 'FK_ProductPurchased_Custo mer') THEN
ALTER TABLE "ProductPurchased" DROP CONSTRAINT "FK_ProductPurchased_Custo mer"
END;
But that's not valid SQL.
Do you know a way to do that with valid PostgreSQL SQL?
I want something like this:
CASE WHEN EXISTS(SELECT constraint_name FROM information_schema.table_c
ALTER TABLE "ProductPurchased" DROP CONSTRAINT "FK_ProductPurchased_Custo
END;
But that's not valid SQL.
Do you know a way to do that with valid PostgreSQL SQL?
..or..
SELECT @some_variable = constraint_name FROM information_schema.table_c onstraints WHERE constraint_name = 'FK_ProductPurchased_Custo mer')
IF @some_variable IS NOT NULL
begin
'do your stuff here
end
SELECT @some_variable = constraint_name FROM information_schema.table_c
IF @some_variable IS NOT NULL
begin
'do your stuff here
end
ASKER
Can you do that in PostgreSQL though? As I understood it, it doesn't have an IF statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My comment was for SQL Server; I didn't read the PostgreSQL requirement of your question. Please disregard my earlier comments.
ASKER
Thank you Dominik! I wouldn't have figured that one out on my own - it was harder than I imagined.
ASKER
Imhorn: No problem, thanks for trying :)
IF (some criteria) THEN
'do this
else
'do that
end if
Instead, use the SQL IF construct (no THEN or END IF required, but good to surround commands with begin..end)
IF EXISTS(SELECT constraint_name FROM information_schema.table_c
begin
ALTER TABLE "ProductPurchased" DROP CONSTRAINT "FK_ProductPurchased_Custo
end