Error Syntax Error At Or Near If Sql State 42601
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this syntax error at or near if postgresql site About Us Learn more about Stack Overflow the company Business Learn more postgres syntax error at or near case about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Psql Do
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PostgreSQL IF
If Exists Postgresql
EXITS inside a FUNCTION Error 42601 up vote 0 down vote favorite I want to create a function that will return an integer variable (or boolean) after check if there are any row in table RESERVATION have an RESERVATION_ID that equal to the reser_id I give to it. But my code seem wrong at the IF statement. This is my query code: CREATE OR query has no destination for result data REPLACE Function fcn_check (reser_id integer) RETURNS integer AS $$ BEGIN IF (EXISTS (SELECT * FROM TICKET_TICKET WHERE reservation_id = reser_id) ) THEN SELECT 1 AS result; ELSE SELECT 0 AS result; END IF; END $$ LANGUAGE SQL; The error messages is: ERROR: syntax error at or near "IF" LINE 2: IF (SELECT * FROM TICKET_TICKET WHERE reservation_id = rese... ^ ********** Error ********** ERROR: syntax error at or near "IF" SQL state: 42601 Character: 76 Im a newbie at pgsql and look like I didnt try hard enough to slove this, but please help me. Many Thanks! function postgresql if-statement syntax-error share|improve this question asked Jun 9 '14 at 3:40 user3721005 31 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted $$ LANGUAGE SQL; You seem to be writing PL/PgSQL, but you've declared it as SQL. Use LANGUAGE plpgsql. share|improve this answer answered Jun 9 '14 at 3:54 Craig Ringer 134k18223306 Oh thank you! I did it, but still dont know how the $$ work, how many keyword use for it? –user3721005 Jun 9 '14 at 6:52 I do not un
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
Postgres If Else
Us Learn more about Stack Overflow the company Business Learn more about hiring postgres case developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up postgresql: syntax error at or near http://stackoverflow.com/questions/24113376/postgresql-if-exits-inside-a-function-error-42601 “DO” up vote 2 down vote favorite 2 I am trying to run a DO block, e.g. this one (copied from a post in this forum) DO $$ DECLARE x int; BEGIN x := 10; RAISE NOTICE '>>>%<<<', x; END; $$ but what happens is: ERROR: syntax error at or near "DO" SQL state: 42601 Character: 1 It doesn't matter, what DO block I run, the DO http://stackoverflow.com/questions/24335981/postgresql-syntax-error-at-or-near-do statement is simply not working (including on examples copy/pasted from the doucment, and I wonder what could be the reason. Could anybody help me, please? Thanks a lot postgresql syntax plpgsql procedure share|improve this question asked Jun 20 '14 at 21:33 jkt 31818 What version of postgres? –hobbs Jun 20 '14 at 21:36 2 Well, I can guess anyway. DO was added in postgres 9.0. For older versions see What's the equivalent of a DO block for Postgres < 9.0? –hobbs Jun 20 '14 at 21:37 Either 9.3. or a later version. –jkt Jun 20 '14 at 21:38 Show the output of select version(); –Clodoaldo Neto Jun 20 '14 at 21:38 2 I am sorry. It is 8.4.20. I am working on a newly installed server and it didn't occur to me that an earlier version could have been installed. Thank you! –jkt Jun 20 '14 at 21:44 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted You're running on an old version of PostgreSQL. In general, if you get an unexpected syntax error on a keyword or PostgreSQL complains about a mis
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings http://stackoverflow.com/questions/29803494/cant-use-an-if-then-else-inside-my-case-statment-sql-state-42601 and policies of this site About Us Learn more about Stack Overflow http://stackoverflow.com/questions/11850194/sql-state-42601-syntax-error-at-or-near-11 the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only syntax error takes a minute: Sign up Can't use an IF … THEN … ELSE inside my CASE statment SQL state: 42601 up vote 0 down vote favorite I would like to building an insert query on the fly. Here I am collecting all column names. Some columns would only need to be scrambled, and some would need to be decrypted, scrambled syntax error at and encrypted. My idea was to check if the column name is in the provided array, and if it is, check to see if its value start with 'pi2'. If so, the apply the necessary transformation decrypt, translate, encrypt. else just translate the value. Here is my query: select case when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e']) then ( if (substring(column_name from 0 for 4) = 'pi2') then 'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))'; else 'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')'; end if; ) else column_name end from information_schema.columns where table_schema = 'my_temp' and table_name = 'table_a' order by ordinal_position ASC Running this query gives me a syntax error: at or near 'then' SQL state: 42601 Character: 177. My question is why can't I use the IF THEN ELSE inside my CASE statement. I have this workaround: select case when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e']) AND substring(column_name from 0 for 4) = 'pi2' then 'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))'; when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e']) then 'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')'; else col
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL state: 42601 syntax error at or near “11” up vote 1 down vote favorite 2 I have a table address_all and it is inherited by several address tables. address_history inherits from parent table history_all and keeps current address information. I am creating new table which inherits address_all table and copies information from address_history to new table. My stored procedure is like this below. I am having some error when I call it. To better explain error I am using line number. 1 CREATE OR REPLACE FUNCTION somefunc() 2 RETURNS void AS 3 $BODY$ 4 DECLARE 5 year_id INTEGER; 6 month_id INTEGER; 7 week_id INTEGER; 8 addresstablename text; 9 backupdays text; 10 BEGIN 11 week_id := EXTRACT(DAY FROM TIMESTAMP 'now()'); 12 month_id := EXTRACT(MONTH FROM TIMESTAMP 'now()'); 13 year_id := EXTRACT(YEAR FROM TIMESTAMP 'now()'); 14 addresstablename := 'address_history_' || week_id || '_' || month_id || '_' || year_id; 15 backupdays:= date_trunc('hour',CURRENT_TIMESTAMP - interval '7 days'); 16 EXECUTE 'create table ' || addresstablename || '() INHERITS (address_all)'; 17 EXECUTE 'insert into ' || addresstablename || ' select * from address_history where address_timestamp >= ' || backupdays || ''; --AS timestamp without time zone); 18 END; 19 $BODY$ 20 LANGUAGE 'plpgsql' VOLATILE; When I run: select somefunc() I get this error: ERROR: syntax error at or near "12" LINE 1: ...story where address_timestamp >= 2012-07-31 12:00:00-0... ^ QUERY: insert into address_history_7_8_2012 select * from address_history where address_timestamp >= 2012-07-31 12:00:00-04 CONTEXT: PL/pgSQL function "somefunc" line 14 at EXECUTE statement ********** Error ********** ERROR: syntax error at or near "12" SQL state: 42601 Context: PL/pgSQL function "somefunc" line 14 at EXECUTE statement postgresql datetime plpgsql dynamic-sql share|improve this question edited Aug 14 '12 at 1:18 Erwin Brandstetter 219k29354465 asked Aug 7 '12 at 16:24 PRP 4352618 add a comment| 3 Answers 3 active oldest votes up vote 5 down vote accepted Try thi