Thread: PL argument max size, and doubt
I was doing some tests to see if I could find a max size for an argument of type TEXT in a PL/PgSQL function (BTW, which it that limit if it exists?). So I made the function to test: CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ BEGIN EXECUTE $ins1$ INSERT INTO funcdatogrande VALUES (default,$ins1$ || quote_literal($1) || $ins2$)$ins2$; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$ LANGUAGE 'plpgsql'; What bothers me is that the INSERT passes ok (the data is inserted) but the function is returning false on any all to it. I hope not to have a conceptual problem.
On Nov 21, 2007 8:23 AM, Martin Marques <martin@marquesminen.com.ar> wrote: > (BTW, which it that limit if it exists?). "In any case, the longest possible character string that can be stored is about 1 GB." See: http://www.postgresql.org/docs/8.2/static/datatype-character.html > So I made the function to test: > > CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ > BEGIN > EXECUTE $ins1$ > INSERT INTO funcdatogrande VALUES (default,$ins1$ || > quote_literal($1) || $ins2$)$ins2$; > IF FOUND THEN > RETURN TRUE; > ELSE > RETURN FALSE; > END IF; > END; > $body$ LANGUAGE 'plpgsql'; > > > What bothers me is that the INSERT passes ok (the data is inserted) but > the function is returning false on any all to it. I hope not to have a > conceptual problem. I don't think EXECUTEing sets FOUND to true. Try: CREATE OR REPLACE FUNCTION DATOGRANDE(TEXT) RETURNS BOOLEAN AS $$ BEGIN INSERT INTO FUNCDATOGRANDE VALUES (DEFAULT,$1); IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE 'PLPGSQL';
Rodrigo De León escribió: > On Nov 21, 2007 8:23 AM, Martin Marques <martin@marquesminen.com.ar> wrote: > >> (BTW, which it that limit if it exists?). >> > > "In any case, the longest possible character string that can be stored > is about 1 GB." > > See: > http://www.postgresql.org/docs/8.2/static/datatype-character.html > I was asking about the limit in the argument. Is it the same as the limits the types have in table definition? >> So I made the function to test: >> >> CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ >> BEGIN >> EXECUTE $ins1$ >> INSERT INTO funcdatogrande VALUES (default,$ins1$ || >> quote_literal($1) || $ins2$)$ins2$; >> IF FOUND THEN >> RETURN TRUE; >> ELSE >> RETURN FALSE; >> END IF; >> END; >> $body$ LANGUAGE 'plpgsql'; >> >> >> What bothers me is that the INSERT passes ok (the data is inserted) but >> the function is returning false on any all to it. I hope not to have a >> conceptual problem. >> > > I don't think EXECUTEing sets FOUND to true. Try: > > CREATE OR REPLACE FUNCTION > DATOGRANDE(TEXT) > RETURNS BOOLEAN AS $$ > BEGIN > INSERT INTO FUNCDATOGRANDE VALUES (DEFAULT,$1); > IF FOUND THEN > RETURN TRUE; > ELSE > RETURN FALSE; > END IF; > END; > $$ LANGUAGE 'PLPGSQL'; > I have always heard that modification queries should be EXECUTED in PL. AFAICR.
Martin Marques <martin@marquesminen.com.ar> writes: > Rodrigo De León escribió: >> "In any case, the longest possible character string that can be stored >> is about 1 GB." >> > I was asking about the limit in the argument. Is it the same as the > limits the types have in table definition? Yeah, ultimately this is a palloc() restriction on the size of any one data value. > I have always heard that modification queries should be EXECUTED in PL. > AFAICR. Run far away from whatever source gave you that advice... regards, tom lane
Tom Lane escribió: > Martin Marques <martin@marquesminen.com.ar> writes: > >> I have always heard that modification queries should be EXECUTED in PL. >> AFAICR. > > Run far away from whatever source gave you that advice... Sorry, it was with DDL commands.
Martin Marques <martin@marquesminen.com.ar> writes: > Tom Lane escribió: >> Martin Marques <martin@marquesminen.com.ar> writes: >>> I have always heard that modification queries should be EXECUTED in PL. >>> AFAICR. >> >> Run far away from whatever source gave you that advice... > Sorry, it was with DDL commands. That's not much better ;-). DDL commands don't have plans, so there's not anything that could be invalidated. I don't see any reason to use an EXECUTE unless there's an actual textual change in the command you need to execute. regards, tom lane
Tom Lane escribió: > Martin Marques <martin@marquesminen.com.ar> writes: >> Tom Lane escribió: >>> Martin Marques <martin@marquesminen.com.ar> writes: >>>> I have always heard that modification queries should be EXECUTED in PL. >>>> AFAICR. >>> Run far away from whatever source gave you that advice... > >> Sorry, it was with DDL commands. > > That's not much better ;-). DDL commands don't have plans, so there's > not anything that could be invalidated. I don't see any reason to use > an EXECUTE unless there's an actual textual change in the command you > need to execute. Well, actually.... http://archives.postgresql.org/pgsql-sql/2007-02/msg00214.php See the follow-ups