Error Missing Loop At End Of Sql Expression
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 Looping function in PostgreSQL up vote -1 down vote favorite I have a table called nba that has the form NBA(date DATE, team1 varchar(30), score1 int, team2 varchar(30), score2 int); where team1 is always the winner and team2 is always the loser. Example part of the table: date | team1 | score1 | team2 | score2 ------------+------------------------+--------+------------------------+-------- 2013-10-29 | Indiana Pacers | 97 | Orlando Magic | 87 2013-10-29 | Miami Heat | 107 | Chicago Bulls | 95 2013-10-29 | Los Angeles Lakers | 116 | Los Angeles Clippers | 103 2013-10-30 | Cleveland Cavaliers | 98 | Brooklyn Nets | 94 2013-10-30 | Philadelphia 76ers | 114 | Miami Heat | 110 I'm trying to write a function that takes in a team, loops through the game list, and transfers the "title" to any team that beats that winning team. For example, the beginning team could be 'Miami Heat'. The next team that beats the heat will be the champion, taking the title away from the heat. The next team to beat that team will pass the title on them, etc. So if the Magic beat the Heat and the Lakers then beat the Magic, the lakers would currently hold the title. I want to iterate through the table and complete this 'passing' of the title all the way through the end and return the last team to have the title at the end of the list. My current attempt looks like this: --Declare function and specify argument as a team that holds title at beginning CREATE FUNCTION findChamp(thechamp varchar(30)) RETURNS void as $func$ -- Set current champ to argument passed in DECLARE r nba%rowtype; champ varchar(30) = thechamp; BEGIN FOR r in Select * from nba LOOP IF team2 = champ -- If the champ lost champ = team1; -- Champ is now the team it lost to END IF; END LOOP; RETURN champ; -- Return the final champ END; $func$ LANGUAGE 'plpgsql'; I'm new to writing functions in
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 7.4.30 Documentation Prev Fast Backward Chapter 37. PL/pgSQL - SQL Procedural Language Fast Forward Next 37.7. Control Structures Control structures are probably the most useful (and important) part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way. 37.7.1. Returning From a Function There are two commands available that allow you to return data from a function: RETURN and RETURN http://stackoverflow.com/questions/22843769/looping-function-in-postgresql NEXT. 37.7.1.1. RETURN RETURN expression; RETURN with an expression terminates the function and returns the value of expression to the caller. This form is to be used for PL/pgSQL functions that do not return a set. When returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as https://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html described for assignments. To return a composite (row) value, you must write a record or row variable as the expression. The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. If you have declared the function to return void, a RETURN statement must still be specified; but in this case the expression following RETURN is optional and will be ignored if present. 37.7.1.2. RETURN NEXT RETURN NEXT expression; When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified in RETURN NEXT commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; in the latter case, an entire "table" of results will be returned. Functions that use RETURN NEXT should be called in the following fashion: SELE
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web http://forums.devshed.com/postgresql-help-21/error-missing-loop-sql-expression-530240.html Developer Tools Advanced Search Forum Databases PostgreSQL Help ERROR: http://postgresql.nabble.com/Please-help-can-t-figure-out-what-s-wrong-with-this-function-td2143493.html missing "LOOP" at end of SQL expression Thread: ERROR: missing "LOOP" at end of SQL expression Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread May error missing 14th, 2008,11:02 AM #1 No Profile Picture rockyp View Profile View Forum Posts Registered User Devshed Newbie (0 - 499 posts) Join Date Sep 2007 Posts 7 Rep Power 0 ERROR: missing "LOOP" at end of SQL expression I'm getting this error in a error missing loop fairly simple function. The error message highlights the LOOP statement... Can anyone see what's wrong with my code? TIA, Rocky ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION "public"."postbailment" (plocationid varchar, pdocument varchar, ptransdate integer) RETURNS "pg_catalog"."void" AS $body$ /* Procedure postbailment Author RP Date 5/13/08 Version 1.00 */ DECLARE TotalCases numeric(12,4) = 0; TotalBottles numeric(12,4) = 0; locBottlesPerCase numeric(12,4) = 0; locCasePrice numeric(8,2) = 0; locLineAmount numeric(8,2) = 0; locSellingCompany varchar(3); locDocument varchar(3); aRow RECORD; bRow RECORD; BEGIN FOR aRow IN SELECT * FROM bad WHERE ((document=pdocument) AND (date = ptransdate) AND locationid=plocationid) ORDER BY productid LOOP <<<<********************* This line is highlited when compiled ********************* /* Get bottles per case */ FOR bRow IN SELECT * FROM cgm WHERE productid = aRow.productid locBottlesPerCase = bRow.bottlespercase; /* Get Price per case */ SELECT INTO locCasePrice caseprice FROM prc WHERE productid = aRow.productid AND transdat
3 messages Moritz Bayer Reply | Threaded Open this post in threaded view ♦ ♦ | Report Content as Inappropriate ♦ ♦ Please help, can't figure out what's wrong with this function... .... Hello group, I 've written the following function: CREATE OR REPLACE FUNCTION "public"."getstadtlandflussentrybyid" (integer) RETURNS SETOF "public"."ty_stadtlandflussentry" AS'DECLARE objReturn ty_stadtlandflussentry; DECLARE iid integer;BEGIN iid := $1; for objReturn IN SELECT ste_id, ste_type, ste_name, ste_firstwrongname, ste_secondwrongname, ste_description, ste_online FROM tbl_stadtlandflussentry WHERE ste_id=iid loop RETURN next objReturn; END LOOP; RETURN;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; When I try to execute the function by calling SELECT getstadtlandflussentrybyid(1); I get the following error: ERROR: missing .. at end of SQL expression I haven't figured out what this message wants to tell me and why it is thrownat all. Maybe someone can give me a hint, Thanks in advance, Moritz PS: the function should run under postgres 7.4and created the following type: CREATE TYPE "public"."ty_stadtlandflussentry" AS ( "ste_id" BIGINT, "ste_type" INTEGER, "ste_name" VARCHAR(100), "ste_firstwrongname" VARCHAR(100), "ste_secondwrongname" VARCHAR(100), "ste_description" TEXT, "ste_online" INTEGER); John DeSoi Reply | Threaded Open this post in threaded view ♦ ♦ | Report Content as Inappropriate ♦ ♦ Re: Please help, can't figure out what's wrong with this function... On Sep 12, 2005, at 8:14 AM, Moritz Bayer wrote: > I get the following error: > ERROR: missing .. at end of SQL expression it looks like your for loop is being interpreted as the integer variant, e.g. for i in 1..10 loop > CREATE TYPE "public"."ty_