Skip site navigation (1) Skip section navigation (2)

Re: Functions

From: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
To: "C K" <shreeseva(dot)it(at)gmail(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Functions
Date: 2008-06-24 16:20:05
Message-ID: 3a0028490806240920i11d19dbu4f9ec39b5111fc59@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(dot)it(at)gmail(dot)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`(at)`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.

In response to

pgsql-admin by date

Next:From: Peter KoczanDate: 2008-06-24 19:24:13
Subject: Re: Database size in Postgresql
Previous:From: Tom LaneDate: 2008-06-24 15:48:11
Subject: Re: plpgsql function seems to be leaking memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group