Problem creating stored procedure

Try
CREATE FUNCTION .....

On Tuesday 27 December 2005 09:41, Ted Byers wrote:

I am puzzled. Can ayone explain why I get an error from Postgres on this
simple stored procedure?

The following is from the pgAdmin III History window:
-- Executing query:
CREATE PROCEDURE addEntity (
fn IN VARCHAR,
ln IN VARCHAR,
ivar IN VARCHAR,
hi IN VARCHAR,
pw IN VARCHAR,
ea IN VARCHAR,
ad IN VARCHAR,
c IN VARCHAR,
p IN VARCHAR,
co IN VARCHAR,
pc IN VARCHAR
)
AS
DECLARE
varID INTEGER
BEGIN
SELECT int varID uid from uids where email_address=ea;
IF varID IS NOT NULL THEN
INSERT INTO addy (uid,address,city,province,country,postal_code)
VALUES (varID,ad,c,p,co,pc)
ELSE
INSERT INTO uids(family_name,first_name,initials,hid,pword,email_address)
VALUES (ln,fn,ivar,hi,pw,ea)
INSERT INTO addys(...) VALUES (currval('seq'),ad,c,p,co,pc)
END IF;
END
LANGUAGE 'sql' VOLATILE;

ERROR: syntax error at or near "PROCEDURE" at character 8

Judging from the examples in the manual (around page 600), my procedure
ought to be fine, but clearly Postgres doesn't like it.

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/

--
UC

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

On 12/27/05, Ted Byers <r.ted.byers@rogers.com> wrote:

I am puzzled. Can ayone explain why I get an error from Postgres on this
simple stored procedure?

The following is from the pgAdmin III History window:
-- Executing query:
CREATE PROCEDURE addEntity (

one reason could be that PROCEDURE's doesn't exist in postgres... you
have to create a FUNCTION...

fn IN VARCHAR,
ln IN VARCHAR,
ivar IN VARCHAR,
hi IN VARCHAR,
pw IN VARCHAR,
ea IN VARCHAR,
ad IN VARCHAR,
c IN VARCHAR,
p IN VARCHAR,
co IN VARCHAR,
pc IN VARCHAR

i think it's [IN|OUT|INOUT] var_name datatype... note the order...

)
AS

needs a $$ sign to begin function

DECLARE
varID INTEGER

needs a semicolon

BEGIN
SELECT int varID uid from uids where email_address=ea;

select into... note the missing "o"

IF varID IS NOT NULL THEN
INSERT INTO addy
(uid,address,city,province,country,postal_code)
VALUES (varID,ad,c,p,co,pc)
ELSE
INSERT INTO
uids(family_name,first_name,initials,hid,pword,email_address)
VALUES (ln,fn,ivar,hi,pw,ea)
INSERT INTO addys(...) VALUES (currval('seq'),ad,c,p,co,pc)
END IF;
END

needs a semicolon

needs a $$ sign to end function

LANGUAGE 'sql' VOLATILE;

it is not sql language but plpgsql

ERROR: syntax error at or near "PROCEDURE" at character 8

Judging from the examples in the manual (around page 600), my procedure
ought to be fine, but clearly Postgres doesn't like it.

maybe are you looking at the examples in how to convert oracle
procedures tu postgres functions? read carefully...

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Tue, Dec 27, 2005 at 12:41:44PM -0500, Ted Byers wrote:

I am puzzled. Can ayone explain why I get an error from Postgres
on this simple stored procedure?

There are several mistakes in the code you posted:

* PostgreSQL doesn't have a CREATE PROCEDURE command. Use CREATE
FUNCTION.

* You didn't declare a return type or any OUT or INOUT parameters.

* You didn't quote the function body.

* Several statements are missing terminating semicolons.

* You wrote "SELECT int" instead of "SELECT INTO".

* You wrote "INSERT INTO addys(...)" instead of providing a column
list. If this is the actual code then it's a syntax error, and
if it's not the actual code then we need to see what you're
really doing.

* You wrote plpgsql code but declared the function to be sql.

Judging from the examples in the manual (around page 600), my
procedure ought to be fine, but clearly Postgres doesn't like it.

What section of the manual are you looking at, and for what version
of PostgreSQL (many of us use the online documentation so page
numbers don't mean anything)? Are you mixing Oracle syntax with
PL/pgSQL syntax?

--
Michael Fuhr

Hi there,

How can i send mail form postgresql.
which language i can use,for that some thing i have to install for postgres.

Kindly suggest.

thanx & regards

aftab

"Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:

Tony> Check out my site at http://www.amsoftwaredesign.com

Tony> and click on the forums link, I have some examples on how to send a
Tony> email from a function using plperl, also how to connect to a simple
Tony> socket server and send messages, plus lots of other function examples,
Tony> some my own and some from the original function cookbook.

I fear for the future.

Did someone forget what a "database" is for?

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

On Tue, 2005-12-27 at 20:58 -0800, Randal L. Schwartz wrote:

"Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:

Tony> Check out my site at http://www.amsoftwaredesign.com

Tony> and click on the forums link, I have some examples on how to send a
Tony> email from a function using plperl, also how to connect to a simple
Tony> socket server and send messages, plus lots of other function examples,
Tony> some my own and some from the original function cookbook.

I fear for the future.

Did someone forget what a "database" is for?

Hah! A few months ago, I got all excited because I made PostgreSQL
connect to an instance of DRb (distributed ruby) and make an IRC bot
talk on freenode when a trigger was called... it was a nice proof of
concept... but in the end I moved that process into the application
layer.

http://rubyurl.com/t3S (installing pl/ruby-untrusted)

http://rubyurl.com/Dz2 (using plruby and rubygems)

In any event... using Ruby from within a PostgreSQL function is a
snap... just hasn't proved itself very useful outside of allowing some
older PHP applications to use some rubygems in the database.

Robby

--
/**************************************************************
* Robby Russell, Founder & Executive Director *
* PLANET ARGON, LLC | www.planetargon.com *
* Ruby on Rails Development, Consulting, and Hosting *
* Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 *
* blog: www.robbyonrails.com | book: www.programmingrails.com *
***************************************************************/

Tony Caduto schrieb:

Randal L. Schwartz wrote:

I fear for the future.

Did someone forget what a "database" is for?

You have nothing to fear but fear itself....
(FDR http://historymatters.gmu.edu/d/5057/)

Let us see why this might be handy(sending a email or connecting to a
socket server).

The email function would be handy to send a notifiction of a certain
event, like a trigger. hmm.. a 60 day notifcation trigger fires on a
financial application, some one needs to be notified.

A real world example of the socket server would be for a notification
system for Pro FTP (which has pg integeration). A file comes in, Pro
FTP writes it to the Postgresql based log table, a trigger fires that
the log has been updated for a new file upload, the PL-perl function
then connects to the pop up notification server and sends a unicast
message to the users connected to the popup notification server.
hmm..pretty handy, you just extended Pro FTP with out having to hack the
Pro FTP source.

This is actually in use in a large corporate setting. while this might
not be a good idea for something out on the internet because of email
latencies etc, it does work extremely well in a setting where that is
not a issue.

And you are sure you can handle a few hundred thousands emails in your
incoming due to a non context aware process which is sending mail?
There is no such thing like a 60 day trigger either ;)
In short its usual better to connect with an application to the
database and handle all that asynchronously - with all the error
handling and sanetizing. You can send mail from server but you
really should not :-)
(Another hint regarding mail: if you send mail to an event and
then the transaction rolls back - how to you hold back the email?
And if sending/connection to the mta fails, do you want your
transaction roll back?)

On Tue, 2005-12-27 at 20:58 -0800, Randal L. Schwartz wrote:

"Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:

Tony> Check out my site at http://www.amsoftwaredesign.com

Tony> and click on the forums link, I have some examples on how to send a
Tony> email from a function using plperl, also how to connect to a simple
Tony> socket server and send messages, plus lots of other function examples,
Tony> some my own and some from the original function cookbook.

I fear for the future.

Did someone forget what a "database" is for?

Hah! A few months ago, I got all excited because I made PostgreSQL
connect to an instance of DRb (distributed ruby) and make an IRC bot
talk on freenode when a trigger was called... it was a nice proof of
concept... but in the end I moved that process into the application
layer.

http://rubyurl.com/t3S (installing pl/ruby-untrusted)

http://rubyurl.com/Dz2 (using plruby and rubygems)

In any event... using Ruby from within a PostgreSQL function is a
snap... just hasn't proved itself very useful outside of allowing some
older PHP applications to use some rubygems in the database.

I'd be perfectly happy having a trigger (or some such thing) that
automagically records, in a table somewhere, messages that I want to
have propagated.

That nicely fits with "what a database is for."

Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
would then grab messages from the table and submit them to [whatever
is the communications layer].

That is a clean sort of design for that sort of thing.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxfinances.info/info/lisp.html
Rules of the Evil Overlord #192. "If I appoint someone as my consort,
I will not subsequently inform her that she is being replaced by a
younger, more attractive woman. <http://www.eviloverlord.com/&gt;

Tony Caduto schrieb:

Tino,
I am talking about corporate apps, there would never be thousands of
emails in the email servers inbound queue from just a few database
applications. (anyway a thousand emails in a Postfix queue is nothing)

Sometimes it is best to send the email right from the client app, but in
many situations sending a email from the client application may be
blocked by corporate firewall restrictions etc, so sending the email
from the server via a trigger or function is the best bet.

Been there. Done that. My email was just about to make you
aware of the problems. You seem "Oh, sending mail from database
is a good idea, lets do that" but actually its not that easy
(but easy done with one of the pl/*u - languages)

How do you stop your server sending mail in case a transaction goes
wild and is sending mails for every updated row in a some megs table?

The email should be the last thing that is called, and in postgresql 8.1
you can use error handling in your function if you so desire to avoid
sending a bogus email if there is a problem.

I'd be surprised how you can avoid sending mail in a transaction but
get it back if the transaction rolls back. Maybe with a queue? But
then - why not just do all the processing outside the database?

And you can code a trigger that fires when there is a difference of 60
days between dates(I have apps that do so...,and the trigger sends a email)

You have your opinion and I have mine, but mine is backed by real
applications running in a corporate environment, and the situations you
describe NEVER occur, so if some one want's to send a email from
Postgresql I don't think they should be turned back by FUD.

*hollow lough* No, we are all playing with toy applications ;) Get real
man, get real :)

I wrote "you can do that" but "its not recommended".

Hint: client applications can run on servers too :-)
So much for "firewall rules" and all the like.

I'd recommend a small easy controllable script which receives
the message signals thru one connection to the server and
LISTEN/NOTIFY and then checks consistency (when did I last
send similar mail etc...) and handles all the mail sending.
This frees up the server early (NOTIFY is rather cheap)
And leaves you with a simple "kill" in case something
goes wrong with your script, w/o disturbing your database
application.

This idea has grown in the practice of a large corp.
application in case you wonder...

Nice days
Tino

On Dec 28, 2005, at 8:28 AM, Christopher Browne wrote:

Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
would then grab messages from the table and submit them to [whatever
is the communications layer].

That is a clean sort of design for that sort of thing.

This is precisely how we build things. Throw in some good locking
mechanisms and you can scale this to incredible levels.

Tony Caduto schrieb:

Tino Wildenhain wrote:

How do you stop your server sending mail in case a transaction goes
wild and is sending mails for every updated row in a some megs table?

It would not be smart to send a email via a trigger that updates or
inserts 1000s of rows would it? All the times I have used PL Perl to
send a email that has NEVER been the case.

I agree that for a super busy server that is running a website or
something I probably would not do it that way(most would send the email
via PHP or whatever). BUT for a GUI client server application(not web
based) it's more than acceptable especially when the emails are being
sent internally to a internal mail server where the connection/send time
is low. It also does not have to be called from a trigger you could
just call it from another function.

It's not that difficult to catch and handle exceptions in a 8.1 or 8.0

We do not talk about exceptions here. I'm talking about transactions.
And you never know who will be aborting a transaction after your
call to the function. No need for referral to the fine manuals :-)

++Tino

----- Original Message -----
From: "Vivek Khera" <vivek@khera.org>
To: "PG-General General" <pgsql-general@postgresql.org>
Sent: Wednesday, December 28, 2005 11:48 AM
Subject: Re: [GENERAL] sending mail from Postgres

On Dec 28, 2005, at 8:28 AM, Christopher Browne wrote:

Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
would then grab messages from the table and submit them to [whatever
is the communications layer].

That is a clean sort of design for that sort of thing.

This is precisely how we build things. Throw in some good locking
mechanisms and you can scale this to incredible levels.

Here is a general question relating to this. The problem involves due
diligence related to environmental protection. Consider a distributed
application to support this. You have a facility with an environmental
monitoring program. Samples are taken from soil, water and air and sent
off-site for analysis. Each sample, when analyzed, results in a report sent
to the facility's management for their records. However, if the
concentration of some contaminant in a sample is above some threshold, a
second report, containing the complete analysis results for the sample, is
generated and sent to one or more people, both inside and outside the
organisation (e.g. engineers within the organization responsible for fixing
problems with the facility and engineers at relevant regulatory agencies).
One objective is to automate as much of the data management as possible and
to ensure that if a problem arises everyone who needs to know about it is
notified. The process has to be auditable, so that information about when
each step in the process starts is stored in the database, as well as
information about when messages are acknowledged (again automated - so when
an engineer opens a message about a problem, an acknowledgement is sent to
the database without his intervention).

I suppose email might work as a means of sending messages, but I was
thinking of Sun's JMS instead, working with triggers. I could then create
my own thin client to display the reports, perhaps sorting them according to
user specified criteria. I can see how to do it within the web tier, or
within the client tier (within the labs doing the analyses). The thing is,
of the designs I have considered, the one involving triggers with JMS on the
main supporting website (with a database back end) is the simplest in terms
of deployment, since all interested parties could interact with the
application through the internet using a very thin client (perhaps even with
an applet within a web page) and I would not need to worry about deploying
software to all relevant people/sites.

If you faced this problem, what would you do, and why?

Cheers,

Ted
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/

"Ted" == Ted Byers <r.ted.byers@rogers.com> writes:

Ted> Here is a general question relating to this. The problem involves due
Ted> diligence related to environmental protection. Consider a distributed
Ted> application to support this. You have a facility with an
Ted> environmental monitoring program. Samples are taken from soil, water
Ted> and air and sent off-site for analysis. Each sample, when analyzed,
Ted> results in a report sent to the facility's management for their
Ted> records. However, if the concentration of some contaminant in a
Ted> sample is above some threshold, a second report, containing the
Ted> complete analysis results for the sample, is generated and sent to one
Ted> or more people, both inside and outside the organisation
Ted> (e.g. engineers within the organization responsible for fixing
Ted> problems with the facility and engineers at relevant regulatory
Ted> agencies). One objective is to automate as much of the data management
Ted> as possible and to ensure that if a problem arises everyone who needs
Ted> to know about it is notified. The process has to be auditable, so
Ted> that information about when each step in the process starts is stored
Ted> in the database, as well as information about when messages are
Ted> acknowledged (again automated - so when an engineer opens a message
Ted> about a problem, an acknowledgement is sent to the database without
Ted> his intervention).

Ted> I suppose email might work as a means of sending messages, but I was
Ted> thinking of Sun's JMS instead, working with triggers. I could then
Ted> create my own thin client to display the reports, perhaps sorting them
Ted> according to user specified criteria. I can see how to do it within
Ted> the web tier, or within the client tier (within the labs doing the
Ted> analyses). The thing is, of the designs I have considered, the one
Ted> involving triggers with JMS on the main supporting website (with a
Ted> database back end) is the simplest in terms of deployment, since all
Ted> interested parties could interact with the application through the
Ted> internet using a very thin client (perhaps even with an applet within
Ted> a web page) and I would not need to worry about deploying software to
Ted> all relevant people/sites.

Ted> If you faced this problem, what would you do, and why?

As already proposed, I'd have a trigger noticing the exceptional
condition post a record to an audit log table (and use NOTIFY). Then
I'd have a notification process manage watching that table, and send
the notices as needed.

That's the right level for this. Database triggers should be about
adding rows, deleting rows, and modifying values in rows to manage
integrity. It's the middleware that's responsible for repackaging
that or acting on changed data.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

Tony Caduto schrieb:

Tino Wildenhain wrote:

We do not talk about exceptions here. I'm talking about transactions.
And you never know who will be aborting a transaction after your
call to the function. No need for referral to the fine manuals :-)

Common Tino, you let users abort transactions? Who else is going to be
aborting transactions besides you the programmer?
I would never allow that as they would mess everything up.

Actually not. There are several reasons why a transaction can
abort - do you cover all your queries in functions?
This would be a little bit unusual, to say at least.
Every query can fail. Its useless to do error
catching in your app if the server actually rolled back
due to an error and yet sent the mail :-)

Just curious, what framework do you use for your frontend
applications?

Have a nice day
Tino

Yep, we try to keep all the sql in functions/procedures as it makes
updates a lot easier and keeps the business logic seperate from the
client application.
We use native compiled front ends (Delphi) for windows and Lazarus for
everything else. When you keep everything in a function/proc it makes
it a piece of cake to tweak the business logic or update it from a dev box.

I know a lot of coders who use PHP etc put the sql right in the scripts
and that is fine since you can update it without a recompile.

If you catch the errors in you functions and recover from them you won't
need to roll anything back, you would just do a commit.

Later,

Tony

Aftab Alam wrote:

Hi there,

How can i send mail form postgresql.
which language i can use,for that some thing i have to install for postgres.

Kindly suggest.

thanx & regards

aftab

Check out my site at http://www.amsoftwaredesign.com

and click on the forums link, I have some examples on how to send a
email from a function using plperl, also how to connect to a simple
socket server and send messages, plus lots of other function examples,
some my own and some from the original function cookbook.

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql

Randal L. Schwartz wrote:

I fear for the future.

Did someone forget what a "database" is for?

You have nothing to fear but fear itself....
(FDR http://historymatters.gmu.edu/d/5057/)

Let us see why this might be handy(sending a email or connecting to a
socket server).

The email function would be handy to send a notifiction of a certain
event, like a trigger. hmm.. a 60 day notifcation trigger fires on a
financial application, some one needs to be notified.

A real world example of the socket server would be for a notification
system for Pro FTP (which has pg integeration). A file comes in, Pro
FTP writes it to the Postgresql based log table, a trigger fires that
the log has been updated for a new file upload, the PL-perl function
then connects to the pop up notification server and sends a unicast
message to the users connected to the popup notification server.
hmm..pretty handy, you just extended Pro FTP with out having to hack the
Pro FTP source.

This is actually in use in a large corporate setting. while this might
not be a good idea for something out on the internet because of email
latencies etc, it does work extremely well in a setting where that is
not a issue.

A database can be used for much more than simply storing data....

Tino,
I am talking about corporate apps, there would never be thousands of
emails in the email servers inbound queue from just a few database
applications. (anyway a thousand emails in a Postfix queue is nothing)

Sometimes it is best to send the email right from the client app, but in
many situations sending a email from the client application may be
blocked by corporate firewall restrictions etc, so sending the email
from the server via a trigger or function is the best bet.

The email should be the last thing that is called, and in postgresql 8.1
you can use error handling in your function if you so desire to avoid
sending a bogus email if there is a problem.

And you can code a trigger that fires when there is a difference of 60
days between dates(I have apps that do so...,and the trigger sends a email)

You have your opinion and I have mine, but mine is backed by real
applications running in a corporate environment, and the situations you
describe NEVER occur, so if some one want's to send a email from
Postgresql I don't think they should be turned back by FUD.

And you can code a trigger that fires when there is a difference of 60
days between dates(I have apps that do so...,and the trigger sends a email)

I guess I should expand on this more, the trigger is fired via a
insert/update into a table called logons, when a user logs on to the
system a record is inserted(if it does not exist) or updated. Once
fired the trigger function then compares dates on the accounts the user
is reviewing, if there is a 60 day difference a email is sent to
managers letting them know that the user has accounts that have not been
reviewed in the last 60 days.

The trigger is of course fired by a insert/delete or update, but the
logic inside determines if the trigger should actually do something or
just be aborted.

Tony