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

From: "Patrick Ng" <patrick(dot)ng(at)zuji(dot)com>
To: "Richard Broersma Jr" <rabroersma(at)yahoo(dot)com>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: RE : How do I compile/test a PL/SQL in Postgresql
Date: 2006-07-20 15:15:13
Message-ID: 631AA2476DFFD047873427369B8C06980332982B@teasgex1.teasin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Patrick Ng 2006-07-20 15:25:33 Re: RE : How do I compile/test a PL/SQL in Postgresql
Previous Message Richard Broersma Jr 2006-07-20 13:47:31 Re: RE : How do I compile/test a PL/SQL in Postgresql