[SQL] Function description
Hi all, I find some 1553 functions in pg_catalog of which only a small part is documented in the manual. Does anyone know where I can find what the others do with maybe an example.
Re: [SQL] Function description
Bart Degryse wrote: Hi all, I find some 1553 functions in pg_catalog of which only a small part is documented in the manual. Does anyone know where I can find what the others do with maybe an example. There's no other documentation (apart from the source) but most are repetitions and conversions: For example "\df int4" returns 8 identically named casts to cover converting from numeric, real, smallint etc. There is a similar list for int2 and most other types in the system. Likewise "\df int*mod" will show a set of modulo-calculation functions for various sizes of integer. So - they're not explicitly documented as separate functions, but they implement operators and public functions that are. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] FUNCTIONs and CASTs
Dean Gibson (DB Administrator) wrote:
On 2008-02-14 15:19, Tom Lane wrote:
It's not exactly clear what you checked, but it works as expected for
me. See test case below, proving that indexscan works just fine with
a parameter declared using %type.
Consider:
CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE
LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) =
aaa';
The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine
in the function prototype. However, specifying it in the function
prototype doesn't appear to help the performance issue:
I get the same result: "works here".
richardh=> CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE TABLE
richardh=> INSERT INTO zzz SELECT generate_series(1,10)::text;
INSERT 0 10
richardh=> CREATE INDEX zzz_aaa_idx ON zzz (aaa);
CREATE INDEX
richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS
zzz.aaa%TYPE
richardh->LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$;
NOTICE: type reference zzz.aaa%TYPE converted to character
NOTICE: type reference zzz.aaa%TYPE converted to character
CREATE FUNCTION
Time: 15.268 ms
richardh=> SELECT dummy2('9');
dummy2
9
(1 row)
Time: 1.962 ms
richardh=> DROP INDEX zzz_aaa_idx;
DROP INDEX
richardh=> SELECT dummy2('9');
dummy2
9
(1 row)
Time: 45.418 ms
What does this do on your machine?
Here is the actual function that caused be heartburn. The types in the
function prototype match EXACTLY the types of the actual parameters
being passed (and I also tried it with the tablename.columnname%TYPE
notation), and yet this function is slow. However, if I replace the
"$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function
is very fast. Note that ALL of the column names in the function below
are indexed, so this function should be very fast (and is, with the CASTs).
Hang on though - this function isn't using %TYPE, it's using explicit
type definitions. If this function is slow, how can it be anything do
with %TYPE ?
CREATE OR REPLACE FUNCTION "Functions".prior_call( CHAR( 10 ),
CHAR( 9 ), DATE) RETURNS BOOLEAN
STABLE RETURNS NULL ON NULL INPUT LANGUAGE SQL AS $SQL$
SELECT COALESCE( (SELECT TRUE
FROMlic_hd
NATURAL JOINlic_en
NATURAL JOINlic_am
WHERE$1 = licensee_id
AND $2
IN( callsign, prev_callsign )
AND $3 >
grant_date
LIMIT 1),
(SELECT TRUE
FROM_preuls
WHERE$1 = licensee_id
AND $2
IN( callsign, prev_callsign )
LIMIT 1),
FALSE )
$SQL$;
So, I think you can see why it would be nice if the
tablename.columnname%TYPE notation could be used in the function body.
Shouldn't be necessary (see above).
I'm not asking for that as an enhancement; rather, I'm trying to
understand what the tablename.columnname%TYPE notation accomplishes,
since specifying it in the function prototype doesn't appear to
accomplish anything (at least for me) over just specifying "TEXT".
It specifies the type of the variable (or parameter) in question. The
reason you can't use %TYPE directly in your SQL is because afaik it's
not SQL - it's a PostgreSQL extension designed to specify variable types
in functions. SQL constructs tend to expect a literal type name.
I'm not sure what your problem is, but it's not the %TYPE operator,
that's clear.
Can you try reproducing the function as a prepared query? That way you
can run EXPLAIN ANALYSE on it and see what's actually happening here.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Function description
Dont' forget to cc: the list Bart :-) Bart Degryse wrote: Thanks for that explanation Richard. But how can I know what they repeat without having documentation. I mean, how about functions like eg regexeqjoinsel, regexeqsel, reltimeeq, reltimege. I can imagine they mimic some other functionality, but how can I know whichone? Well, like I said many implement operators, so they can be obviously be found in pg_operator: SELECT * FROM pg_operator WHERE oprcode = 'reltimeeq'::regproc; The oprleft/right/result columns hold oid numbers for types in pg_type. Casts are listed in pg_cast etc. The fine manuals detail the system catalogues in a chapter "System Catalogs". http://www.postgresql.org/docs/8.3/static/catalogs.html To see how e.g. \dC etc work start psql with -E If it's not documented though, you need to ask two questions: 1. Why am I using it? 2. Will it be there in the next version? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Function description
>>> Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:15 >>> >Dont' forget to cc: the list Bart :-) My mistake, sorry. Normally I only reply to the list and not to the respondent's personal address. Would that be wrong too? > >Bart Degryse wrote: >> Thanks for that explanation Richard. >> But how can I know what they repeat without having documentation. >> I mean, how about functions like eg regexeqjoinsel, regexeqsel, reltimeeq, >> reltimege. >> I can imagine they mimic some other functionality, but how can I know >> whichone? > >Well, like I said many implement operators, so they can be obviously be >found in pg_operator: > >SELECT * FROM pg_operator WHERE oprcode = 'reltimeeq'::regproc; Ok, now I see. Basically, something like this was what I was looking for. > >The oprleft/right/result columns hold oid numbers for types in pg_type. > >Casts are listed in pg_cast etc. > >The fine manuals detail the system catalogues in a chapter "System >Catalogs". >http://www.postgresql.org/docs/8.3/static/catalogs.html I will take a close look at these. > >To see how e.g. \dC etc work start psql with -E > I'm sorry, but I don't have commandline access to the database. That would require an amount of trust and a level of competence our ICT department is incapable of. >If it's not documented though, you need to ask two questions: >1. Why am I using it? Well, at this moment I'm not using any of these functions as I didn't know what they do. You have to admit though that it would by useless to implement some functionality myself that already exists, but that I just didn't know about because I couldn't find it in the manual. Now I know there's only "aliases" to be found, no "undocument treasures". >2. Will it be there in the next version? > >-- > Richard Huxton > Archonet Ltd
Re: [SQL] Function description
Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:15 >>> Dont' forget to cc: the list Bart :-) My mistake, sorry. Normally I only reply to the list and not to the respondent's personal address. Would that be wrong too? Well, some people prefer to reply directly from their inbox, others directly from the list. The convention on the PG lists is reply-to-all. Making sure the list is copied gives others the chance to add to the discussion. To see how e.g. \dC etc work start psql with -E I'm sorry, but I don't have commandline access to the database. That would require an amount of trust and a level of competence our ICT department is incapable of. Well, if you have access to prt 5432 (or whatever you are using) on the DB host you can run psql locally: psql -h As a last resort you can always see how they work on a local copy, then cut + paste the SQL. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function description
Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> >>> To see how e.g. \dC etc work start psql with -E >>> >> I'm sorry, but I don't have commandline access to the database. That would >> require an amount of trust and a level of competence our ICT department is >> incapable of. > >Well, if you have access to prt 5432 (or whatever you are using) on the >DB host you can run psql locally: psql -h All I have is my desktop Windows pc. I work on the database using EMS SQL Manager for PostgreSQL. There I can run whatever statement I want, but it's not psql. What do you exactly mean with "run psql locally"? Should I be able to run some psql.exe from my windows cmd environment. Or should I start something else first? >As a last resort you can always see how they work on a local copy, then >cut + paste the SQL. Here too, I don't know what you mean exactly with "a local copy".
Re: [SQL] Function description
Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> To see how e.g. \dC etc work start psql with -E I'm sorry, but I don't have commandline access to the database. That would require an amount of trust and a level of competence our ICT department is incapable of. Well, if you have access to prt 5432 (or whatever you are using) on the DB host you can run psql locally: psql -h All I have is my desktop Windows pc. I work on the database using EMS SQL Manager for PostgreSQL. There I can run whatever statement I want, but it's not psql. What do you exactly mean with "run psql locally"? Should I be able to run some psql.exe from my windows cmd environment. Or should I start something else first? Well, the windows installer comes with the backend database server, psql.exe, pgadmin, manuals etc. - I don't know if you can just install psql. From psql you can access local or remote databases. Same for the pg_dump/restore command-line tools. Unless EMS is accessing the server via ODBC or similar it should just work. As a last resort you can always see how they work on a local copy, then cut + paste the SQL. Here too, I don't know what you mean exactly with "a local copy". Install PostgreSQL locally, copy the schema from your main server and add some test data. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Function description
Hi Bart, Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> >>> To see how e.g. \dC etc work start psql with -E >>> >> I'm sorry, but I don't have commandline access to the database. That would >> require an amount of trust and a level of competence our ICT department is incapable of. > >Well, if you have access to prt 5432 (or whatever you are using) on the >DB host you can run psql locally: psql -h All I have is my desktop Windows pc. I work on the database using EMS SQL Manager for PostgreSQL. There I can run whatever statement I want, but it's not psql. What do you exactly mean with "run psql locally"? Should I be able to run some psql.exe from my windows cmd environment. Or should I start something else first? The windows installer, available at; http://www.postgresql.org/ftp/win32/ Allows you to install the DB and / OR the tools including psql So you can just install the psql command line tool onto your local machine. Also, Could you ask for a restricted account on the box that runs the DB? Ie an account that only has access to the DB tools and DB you require to use? I use ssh with a limited account to access a postgesql DB running on debian. My account on the server only allows me access to my "home" directory (think your own documents and settings folder - if you're a windows only user...) on the server and the DB's that I have a requirement to use. >As a last resort you can always see how they work on a local copy, then >cut + paste the SQL. Here too, I don't know what you mean exactly with "a local copy". Download the the whole PostreSQL package; http://www.postgresql.org/ftp/win32/ The following link however is a good place to start it contains some really good information and the same link above to download the windows version of PostgreSQL. http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html Then run an instance of PostgreSQL on your desktop PC. That way you have "local" access to the DB and all of it's tools too. You can "play around" with it to your hearts content, run SQL "scenarios" / test functions etc on your own copy of the DB. Then when you're happy - perform the "locally tested" SQL upon your production DB via your admin tool of choice. Hope this helps. -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis PalCare Pty Ltd 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.palcare.com.au begin:vcard fn:Gavin 'Beau' Baumanis n:Baumanis;Gavin 'Beau' org:PalCare Pty. Ltd adr:;;657 Nicholson Street;Carlton North;;3054;Australia email;internet:[EMAIL PROTECTED] title:Senior Application Developer tel;work:+61 -3 9381 4567 tel;cell:+61 -438 545 586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Certified Microsoft Engineer (MCSE)=0D=0A= Post Graduate Certificate( IT Systems)=0D=0A= Trade Qualified: Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Packages
Hi, Is there any concept of Package in postgresql to group functions ? Thanks, Jyoti Seth
Re: [SQL] Function description
I can't install applications on my desktop pc ICT won't install that application on my desktop pc... that would require an amout of trust and... >>> Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 12:17 >>> Well, the windows installer comes with the backend database server, psql.exe, pgadmin, manuals etc. - I don't know if you can just install psql. From psql you can access local or remote databases. Same for the pg_dump/restore command-line tools. Unless EMS is accessing the server via ODBC or similar it should just work. >> As a last resort you can always see how they work on a local copy, then >> cut + paste the SQL. > Here too, I don't know what you mean exactly with "a local copy". Install PostgreSQL locally, copy the schema from your main server and add some test data. -- Richard Huxton Archonet Ltd
Re: [SQL] Function description
>>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 12:46 >>> >The windows installer, available at; >http://www.postgresql.org/ftp/win32/ >Allows you to install the DB and / OR the tools including psql >So you can just install the psql command line tool onto your local machine. I can't install applications on my desktop pc ICT won't install that application on my desktop pc... that would require an amout of trust and... >Also, >Could you ask for a restricted account on the box that runs the DB? >Ie an account that only has access to the DB tools and DB you require to use? I can certainly ask, but I will not get it...that would require an amout of trust and...
Re: [SQL] Function description
Bart, You just need to put forward an appropriate case. It isn't a case of I would like these things. It is, I MUST have these things in order to perform my job. I MUST have a local / development database for testing and educational / learning purposes - unless of course you would like me to use the production server for testing? It isn't up to ICT to tell you what you can and can't have... Sure, they have a role to play - to keep the servers / desktops running - but they don't make policy - just enforce it. you can bet that in order to perform their tasks they have a collection of tools and programs they use. You NEED / MUST have these things in order to successfully do yours. I would speak to my supervisor / manager and get him/her to direct the ICT group to perform the necessary tasks so as to allow you to competently complete your duties - if you don't get any joy out of talking to them yourself first. Always give them the benefit of doubt and ask first. You just may well be surprised. Not to mention it you gives some ammunition with your manager to say; "I have already asked ICT myself but they are unwilling to assist - can you direct them to provide the things I need?". Bart Degryse wrote: >>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 12:46 >>> >The windows installer, available at; >http://www.postgresql.org/ftp/win32/ >Allows you to install the DB and / OR the tools including psql >So you can just install the psql command line tool onto your local machine. I can't install applications on my desktop pc ICT won't install that application on my desktop pc... that would require an amout of trust and... >Also, >Could you ask for a restricted account on the box that runs the DB? >Ie an account that only has access to the DB tools and DB you require to use? I can certainly ask, but I will not get it...that would require an amout of trust and... -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis PalCare Pty Ltd 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.palcare.com.au begin:vcard fn:Gavin 'Beau' Baumanis n:Baumanis;Gavin 'Beau' org:PalCare Pty. Ltd adr:;;657 Nicholson Street;Carlton North;;3054;Australia email;internet:[EMAIL PROTECTED] title:Senior Application Developer tel;work:+61 -3 9381 4567 tel;cell:+61 -438 545 586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Certified Microsoft Engineer (MCSE)=0D=0A= Post Graduate Certificate( IT Systems)=0D=0A= Trade Qualified: Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Packages
As far as I know not like it exists in Oracle. Alternatively you can use a schema per package, but it doesn't solve eg package variables and constants. Personally I just let my functionnames start with the "package name", so I have general_log(...), general_log_err(...), replicate_exact(...), replicate_faulty(...), replicate_invoice(...), ... I have replaced my package constants by writing a const(...) function. I avoice package variables and if I do need them, I put them in a table. >>> "Jyoti Seth" <[EMAIL PROTECTED]> 2008-02-15 12:46 >>> Hi, Is there any concept of Package in postgresql to group functions ? Thanks, Jyoti Seth
Re: [SQL] Function description
>>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 13:33 >>> >I MUST have a local / development database for testing and educational / >learning purposes - unless of course you would like me to use the production >server for testing? I do have a development database, but not locally. And I don't have psql. >It isn't up to ICT to tell you what you can and can't have... Sure, they have >a role to play - to keep the servers / desktops running - but they don't make >policy - just enforce it. In our company they do. >you can bet that in order to perform their tasks they have a collection of >tools and programs they use. Of course they do. >You NEED / MUST have these things in order to successfully do yours. Uptil now, I haven't felt that lacking the use of psql has prevented me in doing my job. Of course sometimes it would be nice to have it, but not necessary. >I would speak to my supervisor / manager and get him/her to direct the ICT >group to perform >the necessary tasks so as to allow you to competently complete your duties - >if >you don't get any joy out of talking to them yourself first. Officially their manager and my manager have the same level in the company's hierarchy. But in reality they run the company as far as anything related to technology is concerned, and we are at best tolerated. And if you were to ask "So why do you stick to that company"... In the region I live there are very few companies offering jobs like mine. There is one very big one with lots of jobs, but since they are semi-gouvernmental, they are not free in setting the wages, so basically they pay much less than private companies. The alternative is to go work farther from home (meaning in the capital). There are lots of jobs there, but they also imply that - I will spend some 2.5 to 3 hours in traffic jams per day. - I won't be able to bring my children to school each morning (I will have to leave home much earlier to get to the capital in time) - I won't be able to pick up my children from school on wednesday noons (too far) - I won't be able to walk my dog at noon (too far) - I will hardly see my children in the evening: by the time I get home, it's bedtime for them So however annoying the situation on this job may be, it's hardly impossible to find an equally well paid job as close to home (a 5 minute drive) as this one.
[SQL] String function to Find how many times str2 is in str1?
Good morning, Is there a string function in PSQL to count how many times one str is in another string? For example, Str1 = "test test caa dtest testing EndofString"; Str2 = " "; select funcName(Str1, Str2); return 5 Because Str1 has 5 Str2. Thanks ! Ly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] String function to Find how many times str2 is in str1?
Pavel Stehule wrote:
Hello
what about
CREATE OR REPLACE FUNCTION Foobar(text, text)
RETURNS integer AS $$
SELECT array_upper(string_to_array($1,$2),1) - 1;
$$ LANGUAGE SQL IMMUTABLE;
On 15/02/2008, Rodrigo E. De León Plicet <[EMAIL PROTECTED]> wrote:
On Fri, Feb 15, 2008 at 11:09 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
> Str1 = "test test caa dtest testing EndofString";
> Str2 = " ";
>
> select funcName(Str1, Str2);
>
> return 5
CREATE OR REPLACE FUNCTION
FOOBAR(TEXT,TEXT)
RETURNS INT AS $$
SELECT(LENGTH($1) - LENGTH(REPLACE($1, $2, ''))) / LENGTH($2) ;
$$ LANGUAGE SQL IMMUTABLE;
SELECT FOOBAR('test test caa dtest testing EndofString', ' ');
foobar
5
(1 row)
If there is not a system func for this, I will do this way.
Thank you for all inputs.
Ly
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] String function to Find how many times str2 is in str1?
On Fri, Feb 15, 2008 at 11:09 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
> Str1 = "test test caa dtest testing EndofString";
> Str2 = " ";
>
> select funcName(Str1, Str2);
>
> return 5
CREATE OR REPLACE FUNCTION
FOOBAR(TEXT,TEXT)
RETURNS INT AS $$
SELECT(LENGTH($1) - LENGTH(REPLACE($1, $2, ''))) / LENGTH($2) ;
$$ LANGUAGE SQL IMMUTABLE;
SELECT FOOBAR('test test caa dtest testing EndofString', ' ');
foobar
5
(1 row)
Good luck.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] String function to Find how many times str2 is in str1?
Hello
what about
CREATE OR REPLACE FUNCTION Foobar(text, text)
RETURNS integer AS $$
SELECT array_upper(string_to_array($1,$2),1) - 1;
$$ LANGUAGE SQL IMMUTABLE;
On 15/02/2008, Rodrigo E. De León Plicet <[EMAIL PROTECTED]> wrote:
> On Fri, Feb 15, 2008 at 11:09 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
> > Str1 = "test test caa dtest testing EndofString";
> > Str2 = " ";
> >
> > select funcName(Str1, Str2);
> >
> > return 5
>
>
> CREATE OR REPLACE FUNCTION
> FOOBAR(TEXT,TEXT)
> RETURNS INT AS $$
> SELECT(LENGTH($1) - LENGTH(REPLACE($1, $2, ''))) / LENGTH($2) ;
> $$ LANGUAGE SQL IMMUTABLE;
>
> SELECT FOOBAR('test test caa dtest testing EndofString', ' ');
>
> foobar
>
> 5
> (1 row)
>
> Good luck.
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] FUNCTIONs and CASTs
On 2008-02-15 01:38, Richard Huxton wrote: Dean Gibson (DB Administrator) wrote: On 2008-02-14 15:19, Tom Lane wrote: It's not exactly clear what you checked, but it works as expected for me. See test case below, proving that indexscan works just fine with a parameter declared using %type. Consider: CREATE TABLE zzz( aaa CHAR( 10 ) ); CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) = aaa'; The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine in the function prototype. However, specifying it in the function prototype doesn't appear to help the performance issue: I get the same result: "works here". richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE richardh->LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$; NOTICE: type reference zzz.aaa%TYPE converted to character NOTICE: type reference zzz.aaa%TYPE converted to character You REMOVED the CAST from the function definition. Yes, if you do that, it works !!! Here is the actual function that caused be heartburn. The types in the function prototype match EXACTLY the types of the actual parameters being passed (and I also tried it with the tablename.columnname%TYPE notation), and yet this function is slow. However, if I replace the "$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function is very fast. Note that ALL of the column names in the function below are indexed, so this function should be very fast (and is, with the CASTs). Hang on though - this function isn't using %TYPE, it's using explicit type definitions. If this function is slow, how can it be anything do with %TYPE ? Again, you are not understanding my point. My point was that specifying tablename.columnname%TYPE notation doesn't help with the performance problem; I have to explicitly cast the parameter in the body of the function. Since I have to do that anyway, why use the tablename.columnname%TYPE notation? I'm not asking for that as an enhancement; rather, I'm trying to understand what the tablename.columnname%TYPE notation accomplishes, since specifying it in the function prototype doesn't appear to accomplish anything (at least for me) over just specifying "TEXT". It specifies the type of the variable (or parameter) in question. So? What does that accomplish, over just using "TEXT"? The reason you can't use %TYPE directly in your SQL is because afaik it's not SQL - it's a PostgreSQL extension designed to specify variable types in functions. SQL constructs tend to expect a literal type name. I'm not sure what your problem is, but it's not the %TYPE operator, that's clear. As I said, I don't have a problem with the function; I modified it to work. My point was, why use the tablename.columnname%TYPE notation when "TEXT" works just as well (for anything that converts to it)??? -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] FUNCTIONs and CASTs
On 2008-02-15 14:32, Tom Lane wrote: "Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: Again, you are not understanding my point. My point was that specifying tablename.columnname%TYPE notation doesn't help with the performance problem; I have to explicitly cast the parameter in the body of the function. The reason for the lack of communication is that no one else believes that premise. Casting a value to the same type it already has is demonstrably a no-op. Casing a TEXT item to a CHAR( 9 ) item isn't a no-op. I've seen this before in "EXPLAIN ..." output, where a search on an indexed column will be sequential because the planner treats the search value as TEXT rather than CHAR( 9 ). Are you saying that no one believes there is a performance difference? Amazing ... Tom, I've privately eMailed you access instructions to one of my DB servers, so you can see for yourself. -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [SQL] FUNCTIONs and CASTs
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > Again, you are not understanding my point. My point was that specifying > tablename.columnname%TYPE notation doesn't help with the performance > problem; I have to explicitly cast the parameter in the body of the > function. The reason for the lack of communication is that no one else believes that premise. Casting a value to the same type it already has is demonstrably a no-op. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] FUNCTIONs and CASTs
On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote: On 2008-02-15 14:32, Tom Lane wrote: Casing a TEXT item to a CHAR( 9 ) item isn't a no-op. I've seen this before in "EXPLAIN ..." output, where a search on an indexed column will be sequential because the planner treats the search value as TEXT rather than CHAR( 9 ). Are you saying that no one believes there is a performance difference? Amazing ... Tom, I've privately eMailed you access instructions to one of my DB servers, so you can see for yourself. OK, it must have been late at 2am when I last ran the tests, as it now seems to work. By "work", I mean that the casting in the function body is (in the particular case I was having an issue with) apparently unnecessary if the types are proper (which includes the table.column%TYPE notation). I'm happy to find that out, since now I can use the table.column%TYPE notation to advantage. What helped confuse me is that the following function apparently DOES need an internal cast: CREATE OR REPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE SQL AS 'SELECT $1'; SELECT zzz( 'abc' ); returns "abc", not "a". Apparently declarations of CHAR(n) are treated as BPCHAR in function prototypes??? -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [SQL] Backward compatibility psql 8.1 to 8.2
I'm running SUSE 10.3. I have to install something behind the back of the package manager to keep two versions of psql around. Its not the end of the world, but it's a hassle... and fiddly in it's own right. A 8.2/8.3 client that could talk to 8.1 would save hassle here. Richard Huxton wrote: > Feasible no doubt, but fiddly and useless to most. > > You can have multiple installations on the same machine. I think most > of the developers do as do many of the rest of us. What platform are > you running? Debian has built-in support for this, but you can do it > yourself for most installations. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Backward compatibility psql 8.1 to 8.2
Up until 8.2 I never used packages because I ran RH or FC and built from source since 6.5.2 or so. I started using packages with ubuntu, and having 8.0, 8.1, and 8.2 installed at the same time, and it's wonderful for troubleshooting issues that might crop up between versions. The Debian / Ubuntu way is really much better than the rpm one. Does anyone who know about packaging know if this a limitation of the packaging spec in rpm, or is there a relatively simple way to get an rpm based machine to run >1 ver of pgsql at a time? On Feb 15, 2008 10:32 PM, Bryce Nesbitt <[EMAIL PROTECTED]> wrote: > I'm running SUSE 10.3. I have to install something behind the back of > the package manager to keep two versions of psql around. Its not the > end of the world, but it's a hassle... and fiddly in it's own right. A > 8.2/8.3 client that could talk to 8.1 would save hassle here. > > Richard Huxton wrote: > > Feasible no doubt, but fiddly and useless to most. > > > > You can have multiple installations on the same machine. I think most > > of the developers do as do many of the rest of us. What platform are > > you running? Debian has built-in support for this, but you can do it > > yourself for most installations. > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
