Re: Install new language - Prev: Re: Are you frustrated with PostgreSQL

I'm going to skip the questions that I don't know the answer.

----- Original Message -----
From: "Terence Chang" <TChang(at)nqueue(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, July 16, 2003 9:05 PM
Subject: Install new language - Prev: Re: [GENERAL] Are you frustrated with
PostgreSQL

> Folks:
>
> I would like to thank for those who answer my questions. My client have
> decide to switch from MySQL to PostgreSQL. I have successfully installed
> PostgreSQL with Cygwin on my XP laptop. It's a good start. I, however,have
> some other questions.
>
> 1. I am viewing the database from both PostgreSQL manager and PgAdmin III.
> can see there are three languages (C, Internal, SQL) in PostgreSQL, but
not
> in PgAdmin III. I login with the same user. Any idea?
>

PgAdmin shows only "procedural languages".
If You execute: SELECT * from pg_language;
You'll se the column "lanispl" and PgAdmin shows only those languages where
lanisp is TRUE.

> 2. Do I have to install new language "pl/pgSQL" in order to write
functions?
>
Yes. Do something like this:

CREATE OR REPLACE FUNCTION plpgsql_call_handler() RETURNS LANGUAGE_HANDLER
AS '$_LIBPATH/plpgsql.dll', 'plpgsql_call_handler' LANGUAGE 'c';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

where $_LIBPATH is /lib/postgresql by default (on cygwin)

> 3. When I start PostgreSQL, I found there are at least 4 instance of
> postgres.exe running as NT services when there is not connection. Each
> single one use different amount of memory. Is that normal?
>
Yes, 3.

> 4. The "stored procedure" term confused me. I did not see any "create
> procedure" statement in the document. I used a lot of procedures/packages
>in Oracle and MS-SQL. Are there such things in PostgreSQL? How do I do that
> in PostgreSQL? Just use "Function"?

Diferent name for same thing.
I like to call triggers as "stored procedures" because they are not
returning data to the user, but difference is only how You declare the
function. If You do something like:

CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS ' ....

it is a trigger function, and if You do :

CREATE OR REPLACE FUNCTION foo() RETURNS int8 AS ' ....

it is function that returns data.

Packages are not supported under PostgreSQL.

>I could not find examples on how to call
> a > "function" like "Exec MyFunctionName(......)".
>

SELECT something FROM myFunction(param1,param2) if function returns
composite type or

SELECT myFunction(param1,param2) if function returns single value.

Of course function can return SETOF(array) of simple or SETOF composite
type.

> 5. I start PostgreSQL with the comman "pg_ctl start -D /var/pgsql/data" I
> got the following message: It says the database was not shut down
properly?
> What I did wrong? I shut down the database with this command "pg_ctl
stop -D
> /var/pgsql/data -m immediate" when no connection exist.
>

Under cygwin, You should start and stop postmaster with "NET START
postmaster" and "NET STOP postmaster" if it is running as service.
I it fail to start, in 99% cases responsible is undeleted "postmaster.pid"
file.
It is an old Cygwin problem. Go to /usr/share/data and delete
"postmaster.pid" file. You must log-on as postgres.

> $ LOG: database system was interrupted at 2003-07-15 23:59:00 USMST
> LOG: checkpoint record is at 0/8C17D0
> LOG: redo record is at 0/8C17D0; undo record is at 0/0; shutdown FALSE
> LOG: next transaction id: 4428; next oid: 25168
> LOG: database system was not properly shut down; automatic recovery in
> progress
>
> LOG: ReadRecord: record with zero length at 0/8C1810
> LOG: redo is not required
> LOG: database system is ready
>

What causes this ? Simple query, function or something else ? Can You be
more specific.

> Can you image that I got my PostgreSQL setup and running in few hours.
>Cool!

And it is even better under linux !
I must confess, it took me more.

If You have trouble with searching through PostgreSQL documentation, I have
compiled searchable CHM file for 7.3.2.
Feel free to request it, but be prepared on 1.5 MB in Your mailbox.

Regards !