Re: RE : How do I compile/test a PL/SQL in Postgresql

Sorry. Pse ignore. My mistake. The error was actually referring to
something else.

Invoking select metahsia.sf_pop_hsia_cal_year_tab();
Does work.

Thanks alot

-----Original Message-----
From: Patrick Ng
Sent: Thursday, July 20, 2006 11:15 PM
To: 'Richard Broersma Jr'; pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql

Hi Richard,

I manage to compile a zero parameter stored function.

However, when I invoke it :

select metahsia.sf_pop_hsia_cal_year_tab();

I got this error :
ERROR: function to_char(integer) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts

It seems that its expecting no parameter to default to at least one
integer parameter. Is this the case? Does this mean that there must
always be at least one parameter for stored function. None of the
documentation seems to explicit mention this.

If I write a stored function (with zero parameters) and make it return
void. It would theoretically behave like a stored procedure, it does a
lot of batch processing on multiple tables and can expect no parameters
(like main program in C and Java programs)

Thank you & best regards

-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma(at)yahoo(dot)com]
Sent: Thursday, July 20, 2006 9:48 PM
To: Patrick Ng; pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql

> psql -U abc -d DB_NAME
> At the Postgresql prompt, I type \i d:\abc.sql
> but got a D:: Permission denied
> I have added MACHINE_NAME\postgres user to d: drive and its subfolders
> but am still getting the above error.
> Do you have any idea what can be wrong? abc.sql is a stored function.

Here is another link for psql commands:
http://www.postgresql.org/docs/8.0/static/app-psql.html

if you notice your error message shows two colons in "D::". Maybe it is
a path problem.
try:

psql> \!pwd --to get your current working directory.

Also from the above page:

Parsing for arguments stops when another unquoted backslash occurs. This
is taken as the beginning
of a new meta-command. The special sequence \\ (two backslashes) marks
the end of arguments and
continues parsing SQL commands, if any. That way SQL and psql commands
can be freely mixed on a
line. But in any case, the arguments of a meta-command cannot continue
beyond the end of the line.

So maybe the path "d:\abc.sql" the single \ is giving you problems.
Maybe try d:\\abc.sql.
or try:

psql> \cd d:\\ --to change to this directory

this simply try:

psql> \i abc.sql

regards,

Richard Broersma Jr.

________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System.