In PostgreSQL, when writing a CREATE FUNCTION statement, you can add the statement OR REPLACE into the CREATE line, so it looks like this CREATE OR REPLACE FUNCTION. This checks if the function exists, and if it doesn't it creates it. If the function does exist, then it replaces it.
With a table CREATE statement, I tried to do the same thing, but I received a syntax error on the OR REPLACE.
Is there a way to check to see if a table exists, if so, then DROP it and then Re-Create it. I want my script to be able to run on databases that don't have the table created yet as well as databases that have the table created, but I need to update the table.
Thanks,
FT
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The DROP statement fails if you always run it first.
Do you have the code that checks to see if a table exists or not? I have tried several things, but the parser doesn't like my if exists statement. I know you can check it by querying pg_tables, but as funny as this sounds, I am struggling with the IF statement syntax and have used examples directly from the PostgreSQL documentation...Thanks in advance for your help...
This is great. So when I am trying to encompass a statement like this into a function, I get a message like this: ERROR: syntax error at or near "IF"
IF (SELECT EXISTS(SELECT relname FROM pg_class WHERE relname = 'Angelina_VR_Data')) == true THEN
SELECT 1
ELSE SELECT 2
END IF;
Can you help me understand why I am getting an error and what is wrong with my code above...I have really been trying to get this to work for the past day or so... Thank you so much for your help. I have a tight deadline and don't have the PostgreSQL experience yet that I need...
You are made me think about the language. I changed the language and now it is working just fine...that was the problem...PLPGSQL...In this area, PostgreSQL is much more acceptable of different server side coding languages...I need to keep that in mind... thanks for the hint... Below is my code, note the last line of the function create that specifies the language: LANGUAGE 'PLPGSQL' VOLATILE SECURITY DEFINER; It used to display LANGUAGE 'SQL'...that was the problem...
CREATE OR REPLACE FUNCTION pbs."getRegStatus"("Suspen
RETURNS bigint AS
$BODY$
BEGIN
IF (SELECT TRIM($1)) = 'S'
THEN
RETURN 2;
ELSE
RETURN 1;
END IF;
END;
$BODY$
LANGUAGE 'PLPGSQL' VOLATILE SECURITY DEFINER;
ALTER FUNCTION pbs."getRegStatus"("Suspen
Business Accounts
Answer for Membership
by: gamebitsPosted on 2007-10-12 at 06:17:15ID: 20065059
Using a script you can check if a table exist if so drop it and then make the new one, but if you know you are going to replace it anyway why don't you just run a drop table script and then the create table script?