Error Syntax Error At Or Near 1 Plpgsql
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 site About Us Learn more about Stack Overflow the company
Error Syntax Error At Or Near Postgresql
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions syntax error at or near $1 postgres Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million sql state: 42601 programmers, just like you, helping each other. Join them; it only takes a minute: Sign up postgres error syntax error at or near “int” when creating a function up vote 3 down vote favorite I am very
Postgresql Declare Variable
new to postgres. I got this error when try to run the following script: CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS 'DECLARE result int; BEGIN SELECT count(*) into result from tbldealercommissions WHERE txtdealercode = $1; if result < 1 then returns 1; else returns 2 ; end if; END; ' LANGUAGE sql VOLATILE; The error is ERROR: syntax error at or near "int" LINE 3: 'DECLARE result int; not sure what cause this
Postgres Create Table
error. Any help is appreciated. postgresql share|improve this question asked Oct 6 '11 at 12:07 Shiver 80126 add a comment| 3 Answers 3 active oldest votes up vote 7 down vote accepted This is unsuitable: LANGUAGE sql use this instead: LANGUAGE plpgsql The syntax you are trying to use is not pure SQL language but the procedural PL/pgSQL language. In PostgreSQL you can install different languages and PL/pgSQL is only primus inter pares in that regard. This also means that you might get the error message, that this language is not installed. In that case use CREATE LANGUAGE plpgsql; which actives it. Depending on the version of PostgreSQL you might need superuser rights to do this step. Have fun. share|improve this answer edited Oct 6 '11 at 13:45 answered Oct 6 '11 at 12:19 A.H. 34.9k96085 Hi A.H, I changed language to plpgsql and got this error:ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. –Shiver Oct 6 '11 at 12:46 Added some explanation in the post. –A.H. Oct 6 '11 at 13:46 1 @Shiver: Since PostgreSQL 9.0, language PL/pgSQL is installed by default. Additionally, you can: CREATE OR REPLACE LANGUAGE plpgsql; to be sure. –Erwin Brandstetter Oct 6 '11 at 15:56 Great tha
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 Syntax error in declaration of PL/pgSQL function http://stackoverflow.com/questions/7674031/postgres-error-syntax-error-at-or-near-int-when-creating-a-function up vote 0 down vote favorite Can anyone help me with this procedure? It's a pretty simple one, just want to insert some data into a table, but pgAdmin is giving me some errors. This is the procedure code: CREATE OR REPLACE FUNCTION FILL_INVOICE2(IN_NUM integer) RETURNS void AS DECLARE counter numeric := 0; BEGIN IF in_num > 1 THEN WHILE counter < 10 LOOP INSERT INTO INVOICE(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,RECORD_DATE,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE) http://stackoverflow.com/questions/27686961/syntax-error-in-declaration-of-pl-pgsql-function VALUES(counter,counter,counter,100,current_date,current_date,1,100,'Telco services',1111); counter := counter + 1; RAISE NOTICE 'The counter is %', counter; END LOOP; END IF; RETURN; END; Error is: ERROR: syntax error at or near "DECLARE counter numeric" LINE 3: DECLARE ^ ********** Error ********** ERROR: syntax error at or near "DECLARE counter numeric" SQL state: 42601 Character: 75" postgresql plpgsql pgadmin procedures share|improve this question edited Dec 29 '14 at 22:58 Erwin Brandstetter 219k29354465 asked Dec 29 '14 at 10:18 Maki 567 declare should be after begin and not with AS. –SMA Dec 29 '14 at 10:21 1 @almasshaikh, incorrect DECLARE comes before BEGIN. postgresql.org/docs/9.3/interactive/plpgsql-structure.html The problem is that what comes after AS should be a string. –Eelke Dec 29 '14 at 10:40 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted This would work: CREATE OR REPLACE FUNCTION fill_invoice2(in_num integer) RETURNS void AS $func$ DECLARE counter numeric := 0; BEGIN IF in_num > 1 THEN WHILE counter < 10 LOOP INSERT INTO invoice(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,RECORD_DATE ,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE) VALUES(counter,counter,counter,100,current_date,current_date ,1,100,'Telco services',1111); counter := counter + 1; RAISE NOTICE 'The counter is %', counter; END LOOP; END IF; END $func$ LANGUAGE plpgsql; Major points Missing language declaration. Missing q
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites https://www.postgresql.org/message-id/565CC1C1.3020601@aklaver.com Propaganda Resources Weekly News Re: (psql 9.4.5) ERROR: syntax error at or near "createuser" From: Adrian Klaver