Functions have 32 args limt ??? | |
Hi,
For my supprise I found that functions have 32 parameter limit.
Where to find more info about this limitation or similar limitations ?
I need at least 50, 100 would be ok.
Real life function below:
CREATE OR REPLACE FUNCTION
wpr_KA_I_PersonCard_Doc(int,varchar,varchar,varcha r,varchar,varchar,varchar,
varchar,varchar,varchar,varchar,varchar,varchar,va rchar,varchar,varchar,varc
har,varchar,varchar,varchar,varchar,timestamp,time stamp,boolean,varchar,time
stamp,timestamp,boolean,varchar,varchar,timestamp, varchar,varchar,varchar,bo
olean)
RETURNS void AS '
DECLARE
@DocAction ALIAS FOR $1;
-- 1 - Load Document
-- 2 - Update Header
@SessionID ALIAS FOR $2; -- Session ID
@LockID ALIAS FOR $3; -- Lock ID
@Lang ALIAS FOR $4; -- Language
@PersonID ALIAS FOR $5;
@MainPersonCode ALIAS FOR $6;
@SurName ALIAS FOR $7;
@FirstName ALIAS FOR $8;
@FatherName ALIAS FOR $9;
@Phone ALIAS FOR $10;
@WorkPhone ALIAS FOR $11;
@GSM ALIAS FOR $12;
@Email ALIAS FOR $13;
@State ALIAS FOR $14;
@CountyCode ALIAS FOR $15;
@CountyName ALIAS FOR $16;
@MunicipalityCode ALIAS FOR $17;
@MunicipalityName ALIAS FOR $18;
@Address ALIAS FOR $19;
@ZipCode ALIAS FOR $20;
@PermissionToResideNr ALIAS FOR $21;
@PermissionToResideFrom ALIAS FOR $22;
@PermissionToResideTo ALIAS FOR $23;
@NotCitizen ALIAS FOR $24;
@WorkPermitNr ALIAS FOR $25;
@WorkPermitFrom ALIAS FOR $26;
@WorkPermitValidTo ALIAS FOR $27;
@NotResident ALIAS FOR $28;
@ResidentState ALIAS FOR $29;
@HeathyCardCode ALIAS FOR $30;
@HeathyCardValidTo ALIAS FOR $31;
@BankAccount ALIAS FOR $32;
@BankCode ALIAS FOR $33;
@Sex ALIAS FOR $34;
@Smoke ALIAS FOR $35;
BEGIN
END;
' LANGUAGE 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Functions have 32 args limt ???
You might fnd a RECORD type better.
Ivar wrote:
[color=blue]
>Hi,
>
>For my supprise I found that functions have 32 parameter limit.
>
>Where to find more info about this limitation or similar limitations ?
>
>
>I need at least 50, 100 would be ok.
>
>Real life function below:
>
>CREATE OR REPLACE FUNCTION
>wpr_KA_I_PersonCard_Doc(int,varchar,varchar,varch ar,varchar,varchar,varchar,
>varchar,varchar,varchar,varchar,varchar,varchar,v archar,varchar,varchar,varc
>har,varchar,varchar,varchar,varchar,timestamp,tim estamp,boolean,varchar,time
>stamp,timestamp,boolean,varchar,varchar,timestamp ,varchar,varchar,varchar,bo
>olean)
> RETURNS void AS '
>DECLARE
>
>
> @DocAction ALIAS FOR $1;
> -- 1 - Load Document
> -- 2 - Update Header
> @SessionID ALIAS FOR $2; -- Session ID
> @LockID ALIAS FOR $3; -- Lock ID
> @Lang ALIAS FOR $4; -- Language
> @PersonID ALIAS FOR $5;
>
> @MainPersonCode ALIAS FOR $6;
> @SurName ALIAS FOR $7;
> @FirstName ALIAS FOR $8;
> @FatherName ALIAS FOR $9;
> @Phone ALIAS FOR $10;
> @WorkPhone ALIAS FOR $11;
> @GSM ALIAS FOR $12;
> @Email ALIAS FOR $13;
> @State ALIAS FOR $14;
> @CountyCode ALIAS FOR $15;
> @CountyName ALIAS FOR $16;
> @MunicipalityCode ALIAS FOR $17;
> @MunicipalityName ALIAS FOR $18;
> @Address ALIAS FOR $19;
> @ZipCode ALIAS FOR $20;
> @PermissionToResideNr ALIAS FOR $21;
> @PermissionToResideFrom ALIAS FOR $22;
> @PermissionToResideTo ALIAS FOR $23;
> @NotCitizen ALIAS FOR $24;
> @WorkPermitNr ALIAS FOR $25;
> @WorkPermitFrom ALIAS FOR $26;
> @WorkPermitValidTo ALIAS FOR $27;
> @NotResident ALIAS FOR $28;
> @ResidentState ALIAS FOR $29;
> @HeathyCardCode ALIAS FOR $30;
> @HeathyCardValidTo ALIAS FOR $31;
> @BankAccount ALIAS FOR $32;
> @BankCode ALIAS FOR $33;
> @Sex ALIAS FOR $34;
> @Smoke ALIAS FOR $35;
>BEGIN
>END;
>' LANGUAGE 'plpgsql';
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend | | | | re: Functions have 32 args limt ???
Ivar wrote:[color=blue]
> For my supprise I found that functions have 32 parameter limit.
>
> Where to find more info about this limitation or similar limitations ?
>
> I need at least 50, 100 would be ok.[/color]
See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
(pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
recompile. But note that you'll need to dump, initdb, and reload your
data. Also note that there are performance and disk usage tradeoffs --
search the mailing list archives from August 2002 for some test results
and discussion.
Joe
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Functions have 32 args limt ???
If I understand right, you cant pass record from client apps, eg. C# or VB.
"Dennis Gearon" <gearond@fireserve.net> wrote in message
news:3F4D5E6D.5030507@fireserve.net...[color=blue]
> You might fnd a RECORD type better.
>
> Ivar wrote:
>[color=green]
> >Hi,
> >
> >For my supprise I found that functions have 32 parameter limit.
> >
> >Where to find more info about this limitation or similar limitations ?
> >
> >
> >I need at least 50, 100 would be ok.
> >
> >Real life function below:
> >
> >CREATE OR REPLACE FUNCTION[/color]
>
>wpr_KA_I_PersonCard_Doc(int,varchar,varchar,varch ar,varchar,varchar,varchar[/color]
,[color=blue]
>
>varchar,varchar,varchar,varchar,varchar,varchar,v archar,varchar,varchar,var[/color]
c[color=blue]
>
>har,varchar,varchar,varchar,varchar,timestamp,tim estamp,boolean,varchar,tim[/color]
e[color=blue]
>
>stamp,timestamp,boolean,varchar,varchar,timestamp ,varchar,varchar,varchar,b[/color]
o[color=blue][color=green]
> >olean)
> > RETURNS void AS '
> >DECLARE
> >
> >
> > @DocAction ALIAS FOR $1;
> > -- 1 - Load Document
> > -- 2 - Update Header
> > @SessionID ALIAS FOR $2; -- Session ID
> > @LockID ALIAS FOR $3; -- Lock ID
> > @Lang ALIAS FOR $4; -- Language
> > @PersonID ALIAS FOR $5;
> >
> > @MainPersonCode ALIAS FOR $6;
> > @SurName ALIAS FOR $7;
> > @FirstName ALIAS FOR $8;
> > @FatherName ALIAS FOR $9;
> > @Phone ALIAS FOR $10;
> > @WorkPhone ALIAS FOR $11;
> > @GSM ALIAS FOR $12;
> > @Email ALIAS FOR $13;
> > @State ALIAS FOR $14;
> > @CountyCode ALIAS FOR $15;
> > @CountyName ALIAS FOR $16;
> > @MunicipalityCode ALIAS FOR $17;
> > @MunicipalityName ALIAS FOR $18;
> > @Address ALIAS FOR $19;
> > @ZipCode ALIAS FOR $20;
> > @PermissionToResideNr ALIAS FOR $21;
> > @PermissionToResideFrom ALIAS FOR $22;
> > @PermissionToResideTo ALIAS FOR $23;
> > @NotCitizen ALIAS FOR $24;
> > @WorkPermitNr ALIAS FOR $25;
> > @WorkPermitFrom ALIAS FOR $26;
> > @WorkPermitValidTo ALIAS FOR $27;
> > @NotResident ALIAS FOR $28;
> > @ResidentState ALIAS FOR $29;
> > @HeathyCardCode ALIAS FOR $30;
> > @HeathyCardValidTo ALIAS FOR $31;
> > @BankAccount ALIAS FOR $32;
> > @BankCode ALIAS FOR $33;
> > @Sex ALIAS FOR $34;
> > @Smoke ALIAS FOR $35;
> >BEGIN
> >END;
> >' LANGUAGE 'plpgsql';
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly | | | | re: Functions have 32 args limt ???
I don't see why default is so small.
"Joe Conway" <mail@joeconway.com> wrote in message
news:3F4D8DE0.1060307@joeconway.com...[color=blue]
> Ivar wrote:[color=green]
> > For my supprise I found that functions have 32 parameter limit.
> >
> > Where to find more info about this limitation or similar limitations ?
> >
> > I need at least 50, 100 would be ok.[/color]
>
> See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
> (pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
> recompile. But note that you'll need to dump, initdb, and reload your
> data. Also note that there are performance and disk usage tradeoffs --
> search the mailing list archives from August 2002 for some test results
> and discussion.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| | | | re: Functions have 32 args limt ???
>>>>> "Ivar" == Ivar <ivar@lumisoft.ee> writes:
Ivar> I don't see why default is so small.
Because any function with even 32 parameters has about 25 too many
parameters.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Functions have 32 args limt ???
Ivar wrote:
[color=blue]
>I don't see why default is so small.
>
>"Joe Conway" <mail@joeconway.com> wrote in message
>news:3F4D8DE0.1060307@joeconway.com...
>
>[color=green]
>>Ivar wrote:
>>
>>[color=darkred]
>>>For my supprise I found that functions have 32 parameter limit.
>>>
>>>Where to find more info about this limitation or similar limitations ?
>>>
>>>I need at least 50, 100 would be ok.
>>>
>>>[/color]
>>See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
>>(pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
>>recompile. But note that you'll need to dump, initdb, and reload your
>>data. Also note that there are performance and disk usage tradeoffs --
>>search the mailing list archives from August 2002 for some test results
>>and discussion.
>>
>>Joe
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>>
>>
>>[/color]
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>[/color]
32 is small? I've never designed a function with more than 12-18, at teh
MOST, arguments.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Functions have 32 args limt ???
It all depends what soft are you doing.
There is for example material card function.
It it designed so bad that it has more than 32 ars.
Why must split this function if behind UI I use it as single function for
adding updateing material ???
CREATE OR REPLACE FUNCTION
wpr_M_I_MaterialCard_Doc(int,varchar,varchar,varch ar,varchar,boolean,boolean
,boolean,varchar,varchar,varchar,varchar,varchar,v archar,varchar,varchar,var
char,varchar,varchar,varchar,numeric,varchar,numer ic,numeric,numeric,numeric
,numeric,numeric,boolean,boolean,boolean,varchar,v archar,varchar,numeric)
RETURNS void AS '
DECLARE
@DocAction ALIAS FOR $1;
-- 1 - Load Document
-- 2 - Update Header
-- 3 - New Document
-- 4 - Document Delete
@SessionID ALIAS FOR $2; -- Session ID
@LockID ALIAS FOR $3; -- Lock ID
@Lang ALIAS FOR $4; -- Language
@DocID ALIAS FOR $5; -- Material Parameter ID
@AltG ALIAS FOR $6; -- 1 kui valitakse Funtsionaalse
Grupeeringu alusel
@NoData ALIAS FOR $7; -- 1, Kui eii vC¤ljastata andmeid
@ProductUpd ALIAS FOR $8; -- 1, Kui avatakse korrigeerimiseks
tootekirjelduse alt
@StocCode ALIAS FOR $9; -- Ladu
@MainGroupCode ALIAS FOR $10; -- PeaGrupp
@SubGroupCode ALIAS FOR $11; -- AlaGrupp
@ExtraGroupCode ALIAS FOR $12; -- TC¤iendav Grupp
@AltMainGroupCode ALIAS FOR $13; -- Funtsionnalne PeaGrupp
@AltSubGroupCode ALIAS FOR $14; -- Funtsionnalne AlaGrupp
@AltExtraGroupCode ALIAS FOR $15; -- Funtsionnalne TC¤iendav Grupp
@MatCode ALIAS FOR $16; -- Kood
@Suffix ALIAS FOR $17; -- Positsioon
@BarCode ALIAS FOR $18; -- Triipkood
@MatName ALIAS FOR $19; -- Nimetus
@MeasureUnit ALIAS FOR $20; -- MŨŨtC¼hik
@InPackage ALIAS FOR $21; -- Cœhikut Pakis
@CurrencyCode ALIAS FOR $22; -- Valuuta
@EtalonPrice ALIAS FOR $23; -- EtalonHind
@Discount ALIAS FOR $24; -- Soodustus
@NullPrice ALIAS FOR $25; -- NullHind
@NullPrice_VAT ALIAS FOR $26; -- NullHind KM-ga
@CostPlusPercent ALIAS FOR $27; -- Juurdehindlus %
@VATPercent ALIAS FOR $28; -- KC¤ibemaksu %
@NotInPriceList ALIAS FOR $29; -- 1, Kui ei kuulu hinnakirja
@Closed ALIAS FOR $30; -- 1, Kui on Suletud
@Product ALIAS FOR $31; -- 1, Kui on Toode
@Warranty ALIAS FOR $32; -- Garantii tekst
@Info ALIAS FOR $33; -- Info
@MatDebit ALIAS FOR $34; -- Materjali Konto
@KASTIS ALIAS FOR $35; --,@KASTIS numeric(18,3) =NULL --
Cœhikut Kastis (Konteineris)
BEGIN
"Dennis Gearon" <gearond@fireserve.net> wrote in message
news:3F4E0C5F.40804@fireserve.net...[color=blue]
> Ivar wrote:
>[color=green]
> >I don't see why default is so small.
> >
> >"Joe Conway" <mail@joeconway.com> wrote in message
> >news:3F4D8DE0.1060307@joeconway.com...
> >
> >[color=darkred]
> >>Ivar wrote:
> >>
> >>
> >>>For my supprise I found that functions have 32 parameter limit.
> >>>
> >>>Where to find more info about this limitation or similar limitations ?
> >>>
> >>>I need at least 50, 100 would be ok.
> >>>
> >>>
> >>See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
> >>(pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
> >>recompile. But note that you'll need to dump, initdb, and reload your
> >>data. Also note that there are performance and disk usage tradeoffs --
> >>search the mailing list archives from August 2002 for some test results
> >>and discussion.
> >>
> >>Joe
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 9: the planner will ignore your desire to choose an index scan if[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> >> joining column's datatypes do not match
> >>
> >>
> >>[/color]
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >[/color]
> 32 is small? I've never designed a function with more than 12-18, at teh
> MOST, arguments.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster | | | | re: Functions have 32 args limt ???
On Thu, 28 Aug 2003, Ivar wrote:
[color=blue]
> I don't see why default is so small.[/color]
Re-read Joe's response. There are performance and disk usage tradeoffs
for raising the limit. I'd suggest looking at the mailing list archives
for the discussion mentioned.
[color=blue]
> "Joe Conway" <mail@joeconway.com> wrote in message
> news:3F4D8DE0.1060307@joeconway.com...[color=green]
> > Ivar wrote:[color=darkred]
> > > For my supprise I found that functions have 32 parameter limit.
> > >
> > > Where to find more info about this limitation or similar limitations ?
> > >
> > > I need at least 50, 100 would be ok.[/color]
> >
> > See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
> > (pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
> > recompile. But note that you'll need to dump, initdb, and reload your
> > data. Also note that there are performance and disk usage tradeoffs --
> > search the mailing list archives from August 2002 for some test results
> > and discussion.
> >
> > Joe
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >[/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Functions have 32 args limt ???
It might be possible to use an array.
Ivar wrote:
[color=blue]
>It all depends what soft are you doing.
>
>There is for example material card function.
>It it designed so bad that it has more than 32 ars.
>Why must split this function if behind UI I use it as single function for
>adding updateing material ???
>
>CREATE OR REPLACE FUNCTION
>wpr_M_I_MaterialCard_Doc(int,varchar,varchar,varc har,varchar,boolean,boolean
>,boolean,varchar,varchar,varchar,varchar,varchar, varchar,varchar,varchar,var
>char,varchar,varchar,varchar,numeric,varchar,nume ric,numeric,numeric,numeric
>,numeric,numeric,boolean,boolean,boolean,varchar, varchar,varchar,numeric)
> RETURNS void AS '
>DECLARE
>
>
> @DocAction ALIAS FOR $1;
> -- 1 - Load Document
> -- 2 - Update Header
> -- 3 - New Document
> -- 4 - Document Delete
> @SessionID ALIAS FOR $2; -- Session ID
> @LockID ALIAS FOR $3; -- Lock ID
> @Lang ALIAS FOR $4; -- Language
> @DocID ALIAS FOR $5; -- Material Parameter ID
> @AltG ALIAS FOR $6; -- 1 kui valitakse Funtsionaalse
>Grupeeringu alusel
> @NoData ALIAS FOR $7; -- 1, Kui eii vC?ljastata andmeid
> @ProductUpd ALIAS FOR $8; -- 1, Kui avatakse korrigeerimiseks
>tootekirjelduse alt
>
> @StocCode ALIAS FOR $9; -- Ladu
> @MainGroupCode ALIAS FOR $10; -- PeaGrupp
> @SubGroupCode ALIAS FOR $11; -- AlaGrupp
> @ExtraGroupCode ALIAS FOR $12; -- TC?iendav Grupp
> @AltMainGroupCode ALIAS FOR $13; -- Funtsionnalne PeaGrupp
> @AltSubGroupCode ALIAS FOR $14; -- Funtsionnalne AlaGrupp
> @AltExtraGroupCode ALIAS FOR $15; -- Funtsionnalne TC?iendav Grupp
> @MatCode ALIAS FOR $16; -- Kood
> @Suffix ALIAS FOR $17; -- Positsioon
> @BarCode ALIAS FOR $18; -- Triipkood
> @MatName ALIAS FOR $19; -- Nimetus
> @MeasureUnit ALIAS FOR $20; -- MŨŨtC?hik
> @InPackage ALIAS FOR $21; -- C?hikut Pakis
> @CurrencyCode ALIAS FOR $22; -- Valuuta
> @EtalonPrice ALIAS FOR $23; -- EtalonHind
> @Discount ALIAS FOR $24; -- Soodustus
> @NullPrice ALIAS FOR $25; -- NullHind
> @NullPrice_VAT ALIAS FOR $26; -- NullHind KM-ga
> @CostPlusPercent ALIAS FOR $27; -- Juurdehindlus %
> @VATPercent ALIAS FOR $28; -- KC?ibemaksu %
> @NotInPriceList ALIAS FOR $29; -- 1, Kui ei kuulu hinnakirja
> @Closed ALIAS FOR $30; -- 1, Kui on Suletud
> @Product ALIAS FOR $31; -- 1, Kui on Toode
> @Warranty ALIAS FOR $32; -- Garantii tekst
> @Info ALIAS FOR $33; -- Info
> @MatDebit ALIAS FOR $34; -- Materjali Konto
> @KASTIS ALIAS FOR $35; --,@KASTIS numeric(18,3) =NULL --
>C?hikut Kastis (Konteineris)
>BEGIN
>
>"Dennis Gearon" <gearond@fireserve.net> wrote in message
>news:3F4E0C5F.40804@fireserve.net...
>
>[color=green]
>>Ivar wrote:
>>
>>
>>[color=darkred]
>>>I don't see why default is so small.
>>>
>>>"Joe Conway" <mail@joeconway.com> wrote in message
>>>news:3F4D8DE0.1060307@joeconway.com...
>>>
>>>
>>>
>>>
>>>>Ivar wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>For my supprise I found that functions have 32 parameter limit.
>>>>>
>>>>>Where to find more info about this limitation or similar limitations ?
>>>>>
>>>>>I need at least 50, 100 would be ok.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
>>>>(pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
>>>>recompile. But note that you'll need to dump, initdb, and reload your
>>>>data. Also note that there are performance and disk usage tradeoffs --
>>>>search the mailing list archives from August 2002 for some test results
>>>>and discussion.
>>>>
>>>>Joe
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 9: the planner will ignore your desire to choose an index scan if
>>>>
>>>>[/color][/color]
>your
>
>[color=green][color=darkred]
>>>> joining column's datatypes do not match
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>>
>>>
>>>
>>>
>>>[/color]
>>32 is small? I've never designed a function with more than 12-18, at teh
>>MOST, arguments.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>[/color]
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match | | | | re: Functions have 32 args limt ???
There are different datatypes and does odbc supports arrays ?
"Dennis Gearon" <gearond@fireserve.net> wrote in message
news:3F4E1DB1.3030206@fireserve.net...
It might be possible to use an array.
Ivar wrote:
[color=blue]
>It all depends what soft are you doing.
>
>There is for example material card function.
>It it designed so bad that it has more than 32 ars.
>Why must split this function if behind UI I use it as single function for
>adding updateing material ???
>
>CREATE OR REPLACE FUNCTION
>wpr_M_I_MaterialCard_Doc(int,varchar,varchar,varc har,varchar,boolean,boolea[/color]
n[color=blue]
>,boolean,varchar,varchar,varchar,varchar,varchar, varchar,varchar,varchar,va[/color]
r[color=blue]
>char,varchar,varchar,varchar,numeric,varchar,nume ric,numeric,numeric,numeri[/color]
c[color=blue]
>,numeric,numeric,boolean,boolean,boolean,varchar, varchar,varchar,numeric)
> RETURNS void AS '
>DECLARE
>
>
> @DocAction ALIAS FOR $1;
> -- 1 - Load Document
> -- 2 - Update Header
> -- 3 - New Document
> -- 4 - Document Delete
> @SessionID ALIAS FOR $2; -- Session ID
> @LockID ALIAS FOR $3; -- Lock ID
> @Lang ALIAS FOR $4; -- Language
> @DocID ALIAS FOR $5; -- Material Parameter ID
> @AltG ALIAS FOR $6; -- 1 kui valitakse Funtsionaalse
>Grupeeringu alusel
> @NoData ALIAS FOR $7; -- 1, Kui eii vC?ljastata andmeid
> @ProductUpd ALIAS FOR $8; -- 1, Kui avatakse korrigeerimiseks
>tootekirjelduse alt
>
> @StocCode ALIAS FOR $9; -- Ladu
> @MainGroupCode ALIAS FOR $10; -- PeaGrupp
> @SubGroupCode ALIAS FOR $11; -- AlaGrupp
> @ExtraGroupCode ALIAS FOR $12; -- TC?iendav Grupp
> @AltMainGroupCode ALIAS FOR $13; -- Funtsionnalne PeaGrupp
> @AltSubGroupCode ALIAS FOR $14; -- Funtsionnalne AlaGrupp
> @AltExtraGroupCode ALIAS FOR $15; -- Funtsionnalne TC?iendav Grupp
> @MatCode ALIAS FOR $16; -- Kood
> @Suffix ALIAS FOR $17; -- Positsioon
> @BarCode ALIAS FOR $18; -- Triipkood
> @MatName ALIAS FOR $19; -- Nimetus
> @MeasureUnit ALIAS FOR $20; -- MUUtC?hik
> @InPackage ALIAS FOR $21; -- C?hikut Pakis
> @CurrencyCode ALIAS FOR $22; -- Valuuta
> @EtalonPrice ALIAS FOR $23; -- EtalonHind
> @Discount ALIAS FOR $24; -- Soodustus
> @NullPrice ALIAS FOR $25; -- NullHind
> @NullPrice_VAT ALIAS FOR $26; -- NullHind KM-ga
> @CostPlusPercent ALIAS FOR $27; -- Juurdehindlus %
> @VATPercent ALIAS FOR $28; -- KC?ibemaksu %
> @NotInPriceList ALIAS FOR $29; -- 1, Kui ei kuulu hinnakirja
> @Closed ALIAS FOR $30; -- 1, Kui on Suletud
> @Product ALIAS FOR $31; -- 1, Kui on Toode
> @Warranty ALIAS FOR $32; -- Garantii tekst
> @Info ALIAS FOR $33; -- Info
> @MatDebit ALIAS FOR $34; -- Materjali Konto
> @KASTIS ALIAS FOR $35; --,@KASTIS numeric(18,3) =NULL --
>C?hikut Kastis (Konteineris)
>BEGIN
>
>"Dennis Gearon" <gearond@fireserve.net> wrote in message
>news:3F4E0C5F.40804@fireserve.net...
>
>[color=green]
>>Ivar wrote:
>>
>>
>>[color=darkred]
>>>I don't see why default is so small.
>>>
>>>"Joe Conway" <mail@joeconway.com> wrote in message
>>>news:3F4D8DE0.1060307@joeconway.com...
>>>
>>>
>>>
>>>
>>>>Ivar wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>For my supprise I found that functions have 32 parameter limit.
>>>>>
>>>>>Where to find more info about this limitation or similar limitations ?
>>>>>
>>>>>I need at least 50, 100 would be ok.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
>>>>(pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
>>>>recompile. But note that you'll need to dump, initdb, and reload your
>>>>data. Also note that there are performance and disk usage tradeoffs --
>>>>search the mailing list archives from August 2002 for some test results
>>>>and discussion.
>>>>
>>>>Joe
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 9: the planner will ignore your desire to choose an index scan if
>>>>
>>>>[/color][/color]
>your
>
>[color=green][color=darkred]
>>>> joining column's datatypes do not match
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>>
>>>
>>>
>>>
>>>[/color]
>>32 is small? I've never designed a function with more than 12-18, at teh
>>MOST, arguments.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>[/color]
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match | | | | re: Functions have 32 args limt ???
[color=blue]
>I'd suggest looking at the mailing list archives[/color]
What I must look for ???
"Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote in message
news:20030828080912.T6403-100000@megazone.bigpanda.com...[color=blue]
>
> On Thu, 28 Aug 2003, Ivar wrote:
>[color=green]
> > I don't see why default is so small.[/color]
>
> Re-read Joe's response. There are performance and disk usage tradeoffs
> for raising the limit. I'd suggest looking at the mailing list archives
> for the discussion mentioned.
>[color=green]
> > "Joe Conway" <mail@joeconway.com> wrote in message
> > news:3F4D8DE0.1060307@joeconway.com...[color=darkred]
> > > Ivar wrote:
> > > > For my supprise I found that functions have 32 parameter limit.
> > > >
> > > > Where to find more info about this limitation or similar limitations[/color][/color][/color]
?[color=blue][color=green][color=darkred]
> > > >
> > > > I need at least 50, 100 would be ok.
> > >
> > > See INDEX_MAX_KEYS defined in src/include/pg_config.h.in
> > > (pg_config_manual.h in postgres 7.4). Change to 64 or whatever and
> > > recompile. But note that you'll need to dump, initdb, and reload your
> > > data. Also note that there are performance and disk usage tradeoffs --
> > > search the mailing list archives from August 2002 for some test[/color][/color][/color]
results[color=blue][color=green][color=darkred]
> > > and discussion.
> > >
> > > Joe
> > >
> > >
> > > ---------------------------(end of[/color][/color][/color]
broadcast)---------------------------[color=blue][color=green][color=darkred]
> > > TIP 9: the planner will ignore your desire to choose an index scan if[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > joining column's datatypes do not match
> > >[/color]
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Functions have 32 args limt ???
Ivar wrote:
[color=blue]
>There are different datatypes and does odbc supports arrays ?
>
>"Dennis Gearon" <gearond@fireserve.net> wrote in message
>news:3F4E1DB1.3030206@fireserve.net...
>It might be possible to use an array.
>[/color]
Some one else will have to answer that.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Functions have 32 args limt ???
Ivar wrote:[color=blue]
> I don't see why default is so small.
>[/color]
Did you even bother to look at the thread I referred to?
There was a lengthy discussion on the pros and cons of various default
settings, and the consensus of the community was 32. If you'd like to
make a cogent argument for why it ought to be higher, by all means do
so. But you'll have to convince quite a few people who have no need for
greater than 32 arguments why they should suffer a performance hit just
because you do.
Joe
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Functions have 32 args limt ???
Ivar wrote:
[color=blue][color=green]
>>I'd suggest looking at the mailing list archives[/color]
>
> What I must look for ???[/color] http://groups.google.com/groups?hl=e...TF-8%26hl%3Den
HTH,
Mike Mascari mascarm@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Functions have 32 args limt ???
[color=blue]
> Did you even bother to look at the thread I referred to?[/color]
What thread ?
You just gave some notes how to come over this, but I think I'll never use
modified source
and not standard release server.
If you see my example of my functions (trying to move ms sql to postgre, all
goes well except it),
is them really so dummy or bad design.
[color=blue]
> greater than 32 arguments why they should suffer a performance hit just
> because you do.[/color]
Are there any real pefrormance difference, what are actual difference(%),
have somebody measured even it ?
"Joe Conway" <mail@joeconway.com> wrote in message
news:3F4E2126.6010902@joeconway.com...[color=blue]
> Ivar wrote:[color=green]
> > I don't see why default is so small.
> >[/color]
>
> Did you even bother to look at the thread I referred to?
>
> There was a lengthy discussion on the pros and cons of various default
> settings, and the consensus of the community was 32. If you'd like to
> make a cogent argument for why it ought to be higher, by all means do
> so. But you'll have to convince quite a few people who have no need for
> greater than 32 arguments why they should suffer a performance hit just
> because you do.
>
> Joe
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match | | | | re: Functions have 32 args limt ???
"Ivar" <ivar@lumisoft.ee> writes:[color=blue]
> Are there any real pefrormance difference, what are actual difference(%),
> have somebody measured even it ?[/color]
You still haven't looked at the thread you were pointed to, have you?
There is another issue besides disk space and performance, which is that
functions with large numbers of positional parameters are just plain bad
style --- it's way too easy to introduce bugs by passing the parameters
in the wrong order. It's usually better to coalesce some of the
parameters into arrays or records. Our awareness of this fact keeps us
from wanting to expend lots of work or resources on making the standard
function argument limit larger.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org | | | | re: Functions have 32 args limt ???
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:24253.1062087102@sss.pgh.pa.us...[color=blue]
> "Ivar" <ivar@lumisoft.ee> writes:[color=green]
> > Are there any real pefrormance difference, what are actual[/color][/color]
difference(%),[color=blue][color=green]
> > have somebody measured even it ?[/color]
>
> You still haven't looked at the thread you were pointed to, have you?
>
> There is another issue besides disk space and performance, which is that
> functions with large numbers of positional parameters are just plain bad
> style --- it's way too easy to introduce bugs by passing the parameters
> in the wrong order. It's usually better to coalesce some of the
> parameters into arrays or records. Our awareness of this fact keeps us
> from wanting to expend lots of work or resources on making the standard
> function argument limit larger.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>[/color]
I found some threads now:
Seems there is big fuss around this.
Table sizes are increasing ok, complaining IO penaly, but no reallife speed
panalty size (%) http://groups.google.com/groups?q=FU....pa.us&rnum=28
{
"Josh Berkus" <josh@agliodbs.com> writes:[color=blue]
> Tom,[color=green]
>> I was surprised that people were dissatisfied with 16 (it was 8 not
>> very long ago...). Needing more strikes me as a symptom of either bad
>> coding practices or missing features of other sorts.[/color]
> No, not really. It's just people wanting to use PL/pgSQL procedures as
> data filters. For example, I have a database with complex
> dependancies and validation rules that I started under 7.0.3, when
> RULES were not an option for such things and triggers were harder to
> write. As a result, I have the interface push new records for, say,
> the CLIENTS table through a PL/pgSQL procedure rather than writing to
> the table directly. Since the table has 18 columns, I need (18 + 2
> for session & user) 20 parameters for this procedure.[/color]
There is another reallife situation where is needed more args.
(Basically functions can't be used for INSERT)
}
[color=blue]
> in the wrong order. It's usually better to coalesce some of the
> parameters into arrays or records.[/color]
How you pass array from c# though odbc to sql server ???
Seems I must wait some time, I'm sure that this limit is removed future
releases.
Just curious how other servers handle this ?
MS SQL defenitely works
Orcale ??
Db2 ??
SAP DB, works
Firebird ??
"Ivar" <ivar@lumisoft.ee> wrote in message news:bil8fc$t0$1@sea.gmane.org...[color=blue]
>[color=green]
> > Did you even bother to look at the thread I referred to?[/color]
> What thread ?
> You just gave some notes how to come over this, but I think I'll never use
> modified source
> and not standard release server.
>
> If you see my example of my functions (trying to move ms sql to postgre,[/color]
all[color=blue]
> goes well except it),
> is them really so dummy or bad design.
>[color=green]
> > greater than 32 arguments why they should suffer a performance hit just
> > because you do.[/color]
> Are there any real pefrormance difference, what are actual difference(%),
> have somebody measured even it ?
>
> "Joe Conway" <mail@joeconway.com> wrote in message
> news:3F4E2126.6010902@joeconway.com...[color=green]
> > Ivar wrote:[color=darkred]
> > > I don't see why default is so small.
> > >[/color]
> >
> > Did you even bother to look at the thread I referred to?
> >
> > There was a lengthy discussion on the pros and cons of various default
> > settings, and the consensus of the community was 32. If you'd like to
> > make a cogent argument for why it ought to be higher, by all means do
> > so. But you'll have to convince quite a few people who have no need for
> > greater than 32 arguments why they should suffer a performance hit just
> > because you do.
> >
> > Joe
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >[/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org | | | | re: Functions have 32 args limt ???
but keep in mind, if Oracle had a hard limit of 32 args, and you needed
33, you'd be hosed, because it's closed source.
PostgreSQL is available in the same format that the developers are working
on, and you can always compile it to handle more than 32 parameters.
Since you can make the change, there's no reason for me and the thousands
of other users who will NEVER use 32 or more args to pay the price in
performance just so you don't have to recompile and reinitdb.
I.e. the majority of users are quite happy with the trade off of
performance / # of args we currently have, and you have it well within
your power to edit the max number of args and recompile, so we should all
be happy to have such a solid, reliable, HACKABLE database at our
disposal.
:-)
On Thu, 28 Aug 2003, Ivar wrote:
[color=blue]
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:24253.1062087102@sss.pgh.pa.us...[color=green]
> > "Ivar" <ivar@lumisoft.ee> writes:[color=darkred]
> > > Are there any real pefrormance difference, what are actual[/color][/color]
> difference(%),[color=green][color=darkred]
> > > have somebody measured even it ?[/color]
> >
> > You still haven't looked at the thread you were pointed to, have you?
> >
> > There is another issue besides disk space and performance, which is that
> > functions with large numbers of positional parameters are just plain bad
> > style --- it's way too easy to introduce bugs by passing the parameters
> > in the wrong order. It's usually better to coalesce some of the
> > parameters into arrays or records. Our awareness of this fact keeps us
> > from wanting to expend lots of work or resources on making the standard
> > function argument limit larger.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >[/color]
>
> I found some threads now:
>
> Seems there is big fuss around this.
> Table sizes are increasing ok, complaining IO penaly, but no reallife speed
> panalty size (%)
>
> http://groups.google.com/groups?q=FU....pa.us&rnum=28
> {
> "Josh Berkus" <josh@agliodbs.com> writes:[color=green]
> > Tom,[color=darkred]
> >> I was surprised that people were dissatisfied with 16 (it was 8 not
> >> very long ago...). Needing more strikes me as a symptom of either bad
> >> coding practices or missing features of other sorts.[/color]
> > No, not really. It's just people wanting to use PL/pgSQL procedures as
> > data filters. For example, I have a database with complex
> > dependancies and validation rules that I started under 7.0.3, when
> > RULES were not an option for such things and triggers were harder to
> > write. As a result, I have the interface push new records for, say,
> > the CLIENTS table through a PL/pgSQL procedure rather than writing to
> > the table directly. Since the table has 18 columns, I need (18 + 2
> > for session & user) 20 parameters for this procedure.[/color]
>
>
> There is another reallife situation where is needed more args.
> (Basically functions can't be used for INSERT)
> }
>[color=green]
> > in the wrong order. It's usually better to coalesce some of the
> > parameters into arrays or records.[/color]
> How you pass array from c# though odbc to sql server ???
>
>
> Seems I must wait some time, I'm sure that this limit is removed future
> releases.
>
> Just curious how other servers handle this ?
> MS SQL defenitely works
> Orcale ??
> Db2 ??
> SAP DB, works
> Firebird ??
>
>
> "Ivar" <ivar@lumisoft.ee> wrote in message news:bil8fc$t0$1@sea.gmane.org...[color=green]
> >[color=darkred]
> > > Did you even bother to look at the thread I referred to?[/color]
> > What thread ?
> > You just gave some notes how to come over this, but I think I'll never use
> > modified source
> > and not standard release server.
> >
> > If you see my example of my functions (trying to move ms sql to postgre,[/color]
> all[color=green]
> > goes well except it),
> > is them really so dummy or bad design.
> >[color=darkred]
> > > greater than 32 arguments why they should suffer a performance hit just
> > > because you do.[/color]
> > Are there any real pefrormance difference, what are actual difference(%),
> > have somebody measured even it ?
> >
> > "Joe Conway" <mail@joeconway.com> wrote in message
> > news:3F4E2126.6010902@joeconway.com...[color=darkred]
> > > Ivar wrote:
> > > > I don't see why default is so small.
> > > >
> > >
> > > Did you even bother to look at the thread I referred to?
> > >
> > > There was a lengthy discussion on the pros and cons of various default
> > > settings, and the consensus of the community was 32. If you'd like to
> > > make a cogent argument for why it ought to be higher, by all means do
> > > so. But you'll have to convince quite a few people who have no need for
> > > greater than 32 arguments why they should suffer a performance hit just
> > > because you do.
> > >
> > > Joe
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >[/color]
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >[/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Functions have 32 args limt ???
Also, it should be noted that an untested or welltested compile option is
just as stable as an untested or welltested runtime option. Using a
compile-time option is not necessarily any more or less risky than a
runtime one. Like any option, it should just be documented and proceed
forward.
Jon
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Functions have 32 args limt ???
[color=blue]
> compile-time option is not necessarily any more or less risky than a
> runtime one. Like any option, it should just be documented and proceed
> forward.[/color]
I agree.
But seems that some parts of postgre isn't designed well.
I haven't found any db soft which supports functions/stored procedures which
has such slow
args limit. Postgre is comparing function speed with others, while having
not noted limitaions.
The bad thing is that there isn't any note on postgre www that there is such
limit.
Users start migrating from other db system, they never think that such
simple thing
can be turn to be such obstacle.
I have messed some weeks with postgre, I like it speed, functionality,...
untill some days ago big
supprise.
"Jonathan Bartlett" <johnnyb@eskimo.com> wrote in message
news:Pine.GSU.4.44.0308281507350.14633-100000@eskimo.com...[color=blue]
> Also, it should be noted that an untested or welltested compile option is
> just as stable as an untested or welltested runtime option. Using a
> compile-time option is not necessarily any more or less risky than a
> runtime one. Like any option, it should just be documented and proceed
> forward.
>
> Jon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly |  |
Similar PostgreSQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,439 network members.
|
