Plpgsql Syntax Error
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 syntax error at or near About Us Learn more about Stack Overflow the company Business Learn more about sql state: 42601 hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Postgresql Declare Variable
the Stack Overflow Community Stack Overflow is a community of 6.2 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 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) 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 220k29357468 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
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 6.2 million programmers, just like you, helping each http://stackoverflow.com/questions/27686961/syntax-error-in-declaration-of-pl-pgsql-function other. Join them; it only takes a minute: Sign up Postgresql syntax error for if statement in a function up vote 1 down vote favorite I am trying to write a function in postgresql and i am getting a syntax error for the if statement: CREATE OR REPLACE FUNCTION getallcampuses(IN a character varying, IN b character varying) RETURNS http://stackoverflow.com/questions/25813052/postgresql-syntax-error-for-if-statement-in-a-function TABLE(campusid charactervarying, campusname character varying) AS $BODY$ BEGIN if $1 = 'PREK' then SELECT * from "SIS_campus"; ELSE BEGIN IF $2 = 'DAll' then SELECT distinct(district_id) || 'ALL' AS CampusID, ' District' AS CampusName UNION SELECT campus_id, name FROM "SIS_campus" WHERE district_name IS NOT NULL order by name; Elsif $2 = 'All' then SELECT campus_id, name FROM "SIS_campus" WHERE district_name IS NOT NULL and isnumeric(name) = 0 order by name; end if; END end if; END $BODY$ LANGUAGE sql; Here is the error: ERROR: syntax error at or near "if" LINE 5: if $1 = 'PREK' then ^ postgresql plpgsql share|improve this question edited Sep 12 '14 at 17:52 Erwin Brandstetter 220k29357468 asked Sep 12 '14 at 16:45 Abhishek 1,33211742 You have a typo at Elsif $2 = 'All' then –Rahul Tripathi Sep 12 '14 at 16:48 1 Thanks for including a complete function body and the exact error message text. That's so rare, and is appreciated. (In future, please put your PostgreSQL version in the text too, thoug
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.1.23 Documentation Prev Up Chapter 39. PL/pgSQL - SQL Procedural Language Next 39.5. Basic Statements In this section and the following ones, we describe https://www.postgresql.org/docs/9.1/static/plpgsql-statements.html all the statement types that are explicitly understood by PL/pgSQL. Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute, as described https://codedump.io/share/HdpgUh33l8a/1/postgresql-plpgsql-function-returns-syntax-error-at-end-of-input in Section 39.5.2 and Section 39.5.3. 39.5.1. Assignment An assignment of a value to a PL/pgSQL variable is written as: variable := expression; As explained previously, the expression in such a statement is evaluated by means of an syntax error SQL SELECT command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record variable, or an element of an array that is a simple variable or field. If the expression's result data type doesn't match the variable's data plpgsql syntax error type, or the variable has a specific size/precision (like char(20)), the result value will be implicitly converted by the PL/pgSQL interpreter using the result type's output-function and the variable type's input-function. Note that this could potentially result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function. Examples: tax := subtotal * 0.06; my_record.user_id := 20; 39.5.2. Executing a Command With No Result For any SQL command that does not return rows, for example INSERT without a RETURNING clause, you can execute the command within a PL/pgSQL function just by writing the command. Any PL/pgSQL variable name appearing in the command text is treated as a parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see Section 39.10.1. When executing a SQL command in this way, PL/pgSQL plans the command just once and re-uses the plan on subsequent executions, for the life of the database connection. The implications of this are discussed in detail in Section 39.10.2. Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL
Go Groovy Haml Handlebars Haskell HTML HTTP Ini iOS Jade Java Javascript jQuery JSON Julia Keyman LaTeX Linux Less LOLCODE Makefile Markdown MATLAB MySQL NASM Node.js NSIS Objective-C Pascal Perl PHP PHP Extras PowerShell Python R React JSX reST (reStructuredText) Rip Ruby Rust SAS Sass (Sass) Sass (Scss) Scala Scheme Smalltalk Smarty SQL Stylus Swift Twig TypeScript Vb.net VHDL Wiki markup YAML Other Search Abner Soong - 5 months ago 94x SQL Question postgresql plpgsql function returns syntax error at end of input I was writing a PlPgSQL function in greenplum and the function needs to transfer the interval which < interval '00:00:00' to a positive value. For example, the interval value -23:57:00 shoule be transfered to 00:03:00. So I wrote the function like this below: CREATE OR REPLACE FUNCTION abstime(timeval INTERVAL)
RETURNS INTERVAL AS $$
BEGIN
IF timeval < INTERVAL '00:00:00' THEN
RETURN timeval + INTERVAL '24:00:00';
ELSE
RETURN timeval;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
after I entered, I got these: ERROR: syntax error at end of input
LINE 1: SELECT
^
QUERY: SELECT
CONTEXT: SQL statement in PL/PgSQL function "abstime" near line 7
I am not sure what's going wrong, and plz help. Pavel Stehule Answer Email {} Share Modern PostgreSQL has little bit better diagnostic: postgres=# CREATE OR REPLACE FUNCTION abstime(timeval INTERVAL) RETURNS INTERVAL AS $$ BEGIN IF timeval < INTERVAL '00:00:00' THEN RETURN timeval + INTERVAL '24:00:00'; ELSE RETURN timeval; END IF; RETURN; --- SHOULD BE REMOVED END; $$ LANGUAGE plpgsql; ERROR: missing expression at or near ";" LINE 9: RETURN; ^ The return on the line 9 is useless and missing a expression. You have to remove line 9. In this case the expression is required in RETURN statement. Source (Stackoverflow) http://stackoverflow.com/questions/373413