Обсуждение: 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,varchar,varchar,varchar,varchar,
varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varc
har,varchar,varchar,varchar,varchar,timestamp,timestamp,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';
You might fnd a RECORD type better. Ivar wrote: >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,varchar,varchar,varchar,varchar, >varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varc >har,varchar,varchar,varchar,varchar,timestamp,timestamp,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 > > >
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
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... > You might fnd a RECORD type better. > > Ivar wrote: > > >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,varchar,varchar,varchar,varchar , > >varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,var c > >har,varchar,varchar,varchar,varchar,timestamp,timestamp,boolean,varchar,tim e > >stamp,timestamp,boolean,varchar,varchar,timestamp,varchar,varchar,varchar,b o > >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 > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
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 your > joining column's datatypes do not match >
>>>>> "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!
How many reallife functions have avg. 5 params ? Must see how are other servers, but MS SQL allow much more params than 32, I'm also sure that Oracle, Db2, ... all support more than 32 params. "Randal L. Schwartz" <merlyn@stonehenge.com> wrote in message news:86r836b8gf.fsf@blue.stonehenge.com... > >>>>> "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 >
Ivar wrote: >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 your >> joining column's datatypes do not match >> >> >> > > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > > 32 is small? I've never designed a function with more than 12-18, at teh MOST, arguments.
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,varchar,varchar,boolean,boolean
,boolean,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,var
char,varchar,varchar,varchar,numeric,varchar,numeric,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...
> Ivar wrote:
>
> >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
your
> >> joining column's datatypes do not match
> >>
> >>
> >>
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
> 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)
>
On Thu, 28 Aug 2003, Ivar wrote: > I don't see why default is so small. 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. > "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 your > > joining column's datatypes do not match > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
It might be possible to use an array. Ivar wrote: >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,varchar,varchar,boolean,boolean >,boolean,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,var >char,varchar,varchar,varchar,numeric,varchar,numeric,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... > > >>Ivar wrote: >> >> >> >>>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 >>>> >>>> >your > > >>>> joining column's datatypes do not match >>>> >>>> >>>> >>>> >>>> >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 4: Don't 'kill -9' the postmaster >>> >>> >>> >>> >>> >>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) >> >> >> > > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > >
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:
>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,varchar,varchar,boolean,boolea
n
>,boolean,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,va
r
>char,varchar,varchar,varchar,numeric,varchar,numeric,numeric,numeric,numeri
c
>,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...
>
>
>>Ivar wrote:
>>
>>
>>
>>>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
>>>>
>>>>
>your
>
>
>>>> joining column's datatypes do not match
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>>
>>>
>>>
>>>
>>>
>>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)
>>
>>
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
---------------------------(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
>I'd suggest looking at the mailing list archives What I must look for ??? "Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote in message news:20030828080912.T6403-100000@megazone.bigpanda.com... > > On Thu, 28 Aug 2003, Ivar wrote: > > > I don't see why default is so small. > > 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. > > > "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 your > > > joining column's datatypes do not match > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Ivar wrote: >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. > Some one else will have to answer that.
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
Ivar wrote: >>I'd suggest looking at the mailing list archives > > What I must look for ??? http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3D4C4A1D.10100%40joeconway.com&rnum=1&prev=/groups%3Fq%3DFUNC_MAX_ARGS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den HTH, Mike Mascari mascarm@mascari.com
> Did you even bother to look at the thread I referred to? 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. > greater than 32 arguments why they should suffer a performance hit just > because you do. 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... > 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 >
"Ivar" <ivar@lumisoft.ee> writes:
> Are there any real pefrormance difference, what are actual difference(%),
> have somebody measured even it ?
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
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:24253.1062087102@sss.pgh.pa.us... > "Ivar" <ivar@lumisoft.ee> writes: > > Are there any real pefrormance difference, what are actual difference(%), > > have somebody measured even it ? > > 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 > 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=FUNC_MAX_ARGS&start=20&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=10867.1018048699%40sss.pgh.pa.us&rnum=28 { "Josh Berkus" <josh@agliodbs.com> writes: > Tom, >> 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. > 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. There is another reallife situation where is needed more args. (Basically functions can't be used for INSERT) } > in the wrong order. It's usually better to coalesce some of the > parameters into arrays or records. 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... > > > Did you even bother to look at the thread I referred to? > 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. > > > greater than 32 arguments why they should suffer a performance hit just > > because you do. > 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... > > 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 > > > > > > > ---------------------------(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 >
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: > "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message > news:24253.1062087102@sss.pgh.pa.us... > > "Ivar" <ivar@lumisoft.ee> writes: > > > Are there any real pefrormance difference, what are actual > difference(%), > > > have somebody measured even it ? > > > > 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 > > > > 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=FUNC_MAX_ARGS&start=20&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=10867.1018048699%40sss.pgh.pa.us&rnum=28 > { > "Josh Berkus" <josh@agliodbs.com> writes: > > Tom, > >> 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. > > 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. > > > There is another reallife situation where is needed more args. > (Basically functions can't be used for INSERT) > } > > > in the wrong order. It's usually better to coalesce some of the > > parameters into arrays or records. > 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... > > > > > Did you even bother to look at the thread I referred to? > > 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. > > > > > greater than 32 arguments why they should suffer a performance hit just > > > because you do. > > 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... > > > 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 > > > > > > > > > > > > > ---------------------------(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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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
> 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. 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... > 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) >
On Friday 29 August 2003 07:39, Ivar wrote: > > 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. > > 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. Hmm - if it caught you out (*) then perhaps it needs to be clearly documented. Would you care to supply some changes to the limitations page: http://www.postgresql.org/users-lounge/limitations.html Maybe also to the manuals - you can find the docs for the next release in the developer's part of the website. You don't need to worry about formats, just some short, clear text - probably the -docs mailing list is the best place to post it. If you don't want to subscribe to docs just for this, then you can pass it to me if you like and I'll pass it along. > I have messed some weeks with postgre, I like it speed, functionality,... > untill some days ago big > supprise. Well, a quick recompile and you're on your way again. * Personally I agree with those that think if your function has 32 parameters you need to rethink your function, but that's not the point. There are two or three people who get caught out by this every year, so it would help if we could let them know beforehand. -- Richard Huxton Archonet Ltd
On Friday 29 August 2003 11:12, Ivar wrote: > > Well, a quick recompile and you're on your way again. > > You forgot that windows users aren't goot at compileing, ... . > Windows user want running system. > I'm sure because of it many windows users won't migrate to linux. Many windows users shouldn't be administering a database. If recompiling a package is unacceptable then you probably want to outsource your admin needs. Open-source solutions aren't Microsoft solutions, and you need to think about them differently. The beauty of open-source is exactly that you have the source and can make these changes. If you had a standard no-source package then you'd have to wait for the next product release to get changes (if at all). Now - if you installed from RPM and need some help compiling PG for the first time then I'm sure we can help. In fact - if this is the case you can use the source RPMs and build your own RPM package that you can install wherever you like. You could even put it on a website so that anyone else who needs 100 parameter functions doesn't have to recompile. > > * Personally I agree with those that think if your function has 32 > > parameters you need to rethink your function, > > You can see my function exaples, what can you suggest ? I couldn't see what it did (I don't remember seeing any code, just declarations). If it's just inserting a record, I can't see why you'd use a function. It's probably a style thing - I tend to use functions for triggers and very occasionally unusually complex select queries. Everything else goes through SQL and is abstracted in the "middleware" layer of my apps. > > Hmm - if it caught you out (*) then perhaps it needs to be clearly > > documented. > > Best way isn't document this, but fix it to support more args - then it > never be problem and doesn't need documenting. Well - if you could come up with a way to do so without impacting other users, I'm sure the developers would listen. Otherwise, it's going to be difficult to persuade them to hurt 99% of users to help the 1% when there is already a simple work-around. -- Richard Huxton Archonet Ltd