pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL argument max size, and doubt
7+ messages / 3 participants
[nested] [flat]
* PL argument max size, and doubt
@ 2007-11-21 13:23 Martin Marques <martin@marquesminen.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Marques @ 2007-11-21 13:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-21 15:29 Rodrigo De León <rdeleonp@gmail.com>
parent: Martin Marques <martin@marquesminen.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Rodrigo De León @ 2007-11-21 15:29 UTC (permalink / raw)
To: Martin Marques <martin@marquesminen.com.ar>; +Cc: pgsql-sql
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';
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-21 17:46 Martin Marques <martin@marquesminen.com.ar>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Marques @ 2007-11-21 17:46 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-21 18:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Marques <martin@marquesminen.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-11-21 18:05 UTC (permalink / raw)
To: Martin Marques <martin@marquesminen.com.ar>; +Cc: Rodrigo De León <rdeleonp@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-22 21:36 Martin Marques <martin@marquesminen.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Marques @ 2007-11-22 21:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rodrigo De León <rdeleonp@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-23 06:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Marques <martin@marquesminen.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-11-23 06:40 UTC (permalink / raw)
To: Martin Marques <martin@marquesminen.com.ar>; +Cc: Rodrigo De León <rdeleonp@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL argument max size, and doubt
@ 2007-11-23 10:37 Martin Marques <martin@marquesminen.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Martin Marques @ 2007-11-23 10:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rodrigo De León <rdeleonp@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-11-23 10:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-21 13:23 PL argument max size, and doubt Martin Marques <martin@marquesminen.com.ar>
2007-11-21 15:29 ` Rodrigo De León <rdeleonp@gmail.com>
2007-11-21 17:46 ` Martin Marques <martin@marquesminen.com.ar>
2007-11-21 18:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-22 21:36 ` Martin Marques <martin@marquesminen.com.ar>
2007-11-23 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-23 10:37 ` Martin Marques <martin@marquesminen.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox