Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 51FD6650F0F for ; Tue, 24 Jun 2008 13:20:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 56392-02 for ; Tue, 24 Jun 2008 13:20:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.154]) by postgresql.org (Postfix) with ESMTP id C4EC7650EDD for ; Tue, 24 Jun 2008 13:20:08 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so1398645fgg.23 for ; Tue, 24 Jun 2008 09:20:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=53ek6O6syKmVUOGRgPb10ZtR+NWk9zH7XL6j2D2HjVw=; b=vkEfuYOtc3WL9FqsR+JR/COvQffDsDFoYARIb5usXeLTY2X2EROzF8sMbsrWRvw7t4 2n3XThYtfophNXwDoX6B504nhxv8QrQNZv7ApK1R0++7kpzSAQ1eIgRL7jlydTzKcXkY k7LQaJJE5hc5cDP/Ploe0FKA8643zQLYEmxfw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=WYNLfcVskRPnMZ8f0+/9ppGBJ0/OXydRGyVx5XdgfjPS0fqoUUYv+Nrh4xMKdZWyhM 4qjjGFkN/HzyXVJbyrZ5h0/MFdmn7DHrP5RfEOHbiM6Kg5ls00XWv5d0pyfNQdYx83dk 9fwBz4F2XrIYwGETH36jlDKyi41o0Kp/N/Txs= Received: by 10.86.73.3 with SMTP id v3mr9123429fga.68.1214324405856; Tue, 24 Jun 2008 09:20:05 -0700 (PDT) Received: by 10.86.36.13 with HTTP; Tue, 24 Jun 2008 09:20:05 -0700 (PDT) Message-ID: <3a0028490806240920i11d19dbu4f9ec39b5111fc59@mail.gmail.com> Date: Tue, 24 Jun 2008 13:20:05 -0300 From: "Rafael Domiciano" To: "C K" Subject: Re: Functions Cc: "pgsql-admin@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14259_33256791.1214324405854" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.191 tagged_above=0 required=5 tests=AWL=0.190, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/209 X-Sequence-Number: 29547 ------=_Part_14259_33256791.1214324405854 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello, There a some differences from MySQL to PostgreSQL. In PostgreSQL we use Functions, what you called "Store Procedures", that do the same thing. In Postgres you can combine the Functions with "Triggers". Triggers are procedures that will be done when you do some U, I or D, you have to specific the operation. In the Functions you can work with some languages: plpgsql (the best, in my oppinion), SQL, C, Ruby, Perl, Python, and so on. So, you have to create a function that does the operation you want, and then you call that function using "select * from [function]". About the $n, you can create an alias for the variable, using clause "DECLARE": DECLARE vardocid alias for $1; An example: CREATE OR REPLACE FUNCTION test (integer) RETURNS integer AS $BODY$ DECLARE vardocid alias for $1; BEGIN update docs set posted = -1 where docid = vardocid; RETURN 1; END $BODY$ LANGUAGE 'plpgsql'; select test (2); 2008/6/24 C K : > Dear Members, > I am switching our ERP and other under development applications from MySQL > to PostgreSQL. While working on this, I got some problems while creating > store procedures. As PG does not support procedures, rather it uses > Functions. While creating a function from MySQL as > > CREATE DEFINER=`root`@`localhost` PROCEDURE `postdoc`(vardocid int) > MODIFIES SQL DATA > UPDATE docs SET posted = -1 WHERE docid = vardocid; > > on PG, it gave me error for using variable in where condition. Other things > are properly defined thru' PGAdmin. Does PG do no support to use variables > or variable names in the functions? As per manual for 8.3, I understood to > use $1, $2...$n for the input parameter reference, but it is difficult to > mark them correclty in a big function having many parameters. > How could I use varibles in the functions? > Plesae help and give the details on above issue. > > Thanks > > CPK > -- > Keep your Environment clean and green. ------=_Part_14259_33256791.1214324405854 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello,

There a some differences from MySQL to PostgreSQL. In PostgreSQL we use Functions, what you called "Store Procedures", that do the same thing.
In Postgres you can combine the Functions with "Triggers".
Triggers are procedures that will be done when you do some U, I or D, you have to specific the operation.
In the Functions you can work with some languages: plpgsql (the best, in my oppinion), SQL, C, Ruby, Perl, Python, and so on.
So, you have to create a function that does the operation you want, and then you call that function using "select * from [function]".


About the $n, you can create an alias for the variable, using clause "DECLARE":
DECLARE
  vardocid alias for $1;

An example:
CREATE OR REPLACE FUNCTION test (integer) RETURNS integer AS
$BODY$
DECLARE
  vardocid alias for $1;
BEGIN
  update docs
  set
    posted = -1
  where
    docid = vardocid;

  RETURN 1;
END
$BODY$
LANGUAGE 'plpgsql';

select test (2);

2008/6/24 C K <shreeseva.it@gmail.com>:
Dear Members,
I am switching our ERP and other under development applications from MySQL to PostgreSQL. While working on this, I got some problems while creating store procedures. As PG does not support procedures, rather it uses Functions. While creating a function from MySQL as

CREATE DEFINER=`root`@`localhost` PROCEDURE `postdoc`(vardocid int)
    MODIFIES SQL DATA
UPDATE docs        SET posted = -1        WHERE docid = vardocid;

on PG, it gave me error for using variable in where condition. Other things are properly defined thru' PGAdmin. Does PG do no support to use variables or variable names in the functions? As per manual for 8.3, I understood to use $1, $2...$n for the input parameter reference, but it is difficult to mark them correclty in a big function having many parameters.
How could I use varibles in the functions?
Plesae help and give the details on above issue.

Thanks

CPK
--
Keep your Environment clean and green.

------=_Part_14259_33256791.1214324405854--