Re: [SQL] FUNCTION problem
Adrian Klaver wrote: On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) Ah!, I see what you mean about the definition of 'RECORD'. (The lights come on...) And here I thought it would all be so simple. You show a valid, and most informative solution. This should get things working for me. Thank you very much for your help. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the as test(c1 int,c2 int) clause. At this point it becomes a A--B--C problem i.e determine what your inputs are, how you want to process them and how you want to return the output. '8.1+'?? Hmmm, I'm using 8.3. I could use that. I got the more complex version of the query to work by backing away from 'plpgsql' as the language and using 'sql' instead. I then nested (terribly ugly) my select statements to generate a single SQL query from all. This allows me to change the output of the query without needing to define a new set of output 'OUT' parameters each time I change things. I have use of the 'OUT' parameters with another set of functions though. Thanks for that. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: Did you happen to catch this: Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause Try: select * from test_function(1) I did miss that, but using that method to query the function didn't work either. Postgres doesn't see the result as a tabular set of records. Even if I replace the FOR loop with: quote FOR R IN SELECT * FROM pg_database LOOP RETURN NEXT R; END LOOP; /quote I get the same error(s). I don't think postgres likes the unrelated 'SELECT INTO variable [column] FROM [QUERY] LIMIT 1' lines before the FOR loop... I think I need to go back and approach the function from a different direction. Thanks for all the pointers. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] FUNCTION problem
Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF RECORD AS $BODY$ DECLARE croid integer; BEGIN --PERFORM A SMALL CALCULATION --DOESNT SEEM TO MATTER WHAT IT IS SELECT INTO croid 2; --A SELECT STATEMENT OUTPUTS RECORDS (one in this case) SELECT croid,$1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE --The call looks like the following: SELECT test_function(1); --The resulting error reads as follows: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function test_function line 5 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function test_function line 5 at SQL statement -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Proper entry of polygon type data
Hello, I would like to use 'polygon' type data and am wondering about the entry format of the vertex coordinates. Are the coordinates of the polygon type to be entered one entry per polygon vertex, or one entry per polygon edge segment? For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? ie: INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) ); Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
