By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Functions have 32 args limt ???

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
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,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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a

If I understand right, you cant pass record from client apps, eg. C# or VB.

"Dennis Gearon" <ge*****@fireserve.net> wrote in message
news:3F**************@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,varch ar,varchar,varchar,varchar

,
varchar,varchar,varchar,varchar,varchar,varchar,v archar,varchar,varchar,var c
har,varchar,varchar,varchar,varchar,timestamp,tim estamp,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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #4

P: n/a

I don't see why default is so small.

"Joe Conway" <ma**@joeconway.com> wrote in message
news:3F**************@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

Nov 11 '05 #5

P: n/a
>>>>> "Ivar" == Ivar <iv**@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
<me****@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

Nov 11 '05 #6

P: n/a
Ivar wrote:
I don't see why default is so small.

"Joe Conway" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org)

Nov 11 '05 #7

P: n/a

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" <ge*****@fireserve.net> wrote in message
news:3F************@fireserve.net...
Ivar wrote:
I don't see why default is so small.

"Joe Conway" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #8

P: n/a

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" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org)

Nov 11 '05 #9

P: n/a
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,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" <ge*****@fireserve.net> wrote in message
news:3F************@fireserve.net...

Ivar wrote:
I don't see why default is so small.

"Joe Conway" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@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

Nov 11 '05 #10

P: n/a

There are different datatypes and does odbc supports arrays ?

"Dennis Gearon" <ge*****@fireserve.net> wrote in message
news:3F**************@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,varc har,varchar,boolean,boolea n,boolean,varchar,varchar,varchar,varchar,varchar, varchar,varchar,varchar,va rchar,varchar,varchar,varchar,numeric,varchar,nume ric,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" <ge*****@fireserve.net> wrote in message
news:3F************@fireserve.net...

Ivar wrote:
I don't see why default is so small.

"Joe Conway" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@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


---------------------------(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

Nov 11 '05 #11

P: n/a
I'd suggest looking at the mailing list archives What I must look for ???
"Stephan Szabo" <ss****@megazone.bigpanda.com> wrote in message
news:20*************************@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" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #12

P: n/a
Ivar wrote:
There are different datatypes and does odbc supports arrays ?

"Dennis Gearon" <ge*****@fireserve.net> wrote in message
news:3F**************@fireserve.net...
It might be possible to use an array.

Some one else will have to answer that.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #13

P: n/a
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

Nov 11 '05 #14

P: n/a
Ivar wrote:
I'd suggest looking at the mailing list archives


What I must look for ???


http://groups.google.com/groups?hl=e...TF-8%26hl%3Den

HTH,

Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #15

P: n/a
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" <ma**@joeconway.com> wrote in message
news:3F**************@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

Nov 11 '05 #16

P: n/a
"Ivar" <iv**@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 ma*******@postgresql.org

Nov 11 '05 #17

P: n/a
"Tom Lane" <tg*@sss.pgh.pa.us> wrote in message
news:24**************@sss.pgh.pa.us...
"Ivar" <iv**@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 ma*******@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=FU....pa.us&rnum=28
{
"Josh Berkus" <jo**@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" <iv**@lumisoft.ee> wrote in message news:bi*********@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" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org

Nov 11 '05 #18

P: n/a
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" <tg*@sss.pgh.pa.us> wrote in message
news:24**************@sss.pgh.pa.us...
"Ivar" <iv**@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 ma*******@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=FU....pa.us&rnum=28
{
"Josh Berkus" <jo**@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" <iv**@lumisoft.ee> wrote in message news:bi*********@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" <ma**@joeconway.com> wrote in message
news:3F**************@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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #19

P: n/a
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 ma*******@postgresql.org)

Nov 11 '05 #20

P: n/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. 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" <jo*****@eskimo.com> wrote in message
news:Pi**************************************@eski mo.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 ma*******@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.