Thread: 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

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

Bart Degryse wrote:
>>>> Richard Huxton <dev@archonet.com> 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 <db-hostname>

As a last resort you can always see how they work on a local copy, then 
cut + paste the SQL.

--   Richard Huxton  Archonet Ltd

Bart Degryse wrote:
>>>>> Richard Huxton <dev@archonet.com> 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 <db-hostname>
>  
> 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

Hi Bart,

Bart Degryse wrote:

>>>> Richard Huxton <dev@archonet.com> 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 <db-hostname>

 

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: beau@palcare.com.au

W: http://www.palcare.com.au

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 <gavinb@eclinic.com.au> 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: beau@palcare.com.au

W: http://www.palcare.com.au