pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions
12+ messages / 7 participants
[nested] [flat]
* Functions
@ 2006-11-27 08:56 Doron Baranes <doron.baranes@dbnet.co.il>
0 siblings, 1 reply; 12+ messages in thread
From: Doron Baranes @ 2006-11-27 08:56 UTC (permalink / raw)
To: pgsql-admin
Hi,
I'm new to postgres and i cant find how can i get a function code from the database.
10x
Doron
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions
@ 2006-11-27 09:33 imad <immaad@gmail.com>
parent: Doron Baranes <doron.baranes@dbnet.co.il>
0 siblings, 0 replies; 12+ messages in thread
From: imad @ 2006-11-27 09:33 UTC (permalink / raw)
To: Doron Baranes <doron.baranes@dbnet.co.il>; +Cc: pgsql-admin
select prosrc from pg_proc where proname = '<function_name>';
However you cannot see the code of C language function with this query.
is that what you wanted?
--Imad
www.EnterpriseDB.com
On 11/27/06, Doron Baranes <doron.baranes@dbnet.co.il> wrote:
>
> Hi,
>
> I'm new to postgres and i cant find how can i get a function code from the database.
>
> 10x
> Doron
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Functions
@ 2008-06-24 10:43 C K <shreeseva.it@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: C K @ 2008-06-24 10:43 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions
@ 2008-06-24 16:20 Rafael Domiciano <rafael.domiciano@gmail.com>
parent: C K <shreeseva.it@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Rafael Domiciano @ 2008-06-24 16:20 UTC (permalink / raw)
To: C K <shreeseva.it@gmail.com>; +Cc: 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.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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* functions
@ 2008-08-02 09:39 C K <shreeseva.it@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: C K @ 2008-08-02 09:39 UTC (permalink / raw)
To: pgsql-admin
Dear Friends,
I have written a function as follows. Here I need to get the sum of balance
before the user provided date. The below function works well, but returns
records as a set of values, rather I need individual columns. What to do?
CREATE OR REPLACE FUNCTION aa(date)
RETURNS SETOF record AS
$BODY$SELECT docacctransactions.accgroupid, Sum(docacctransactions.credit)
AS OSC, Sum(docacctransactions.debit) AS OSD
FROM docs INNER JOIN docacctransactions ON docs.docid =
docacctransactions.docid
WHERE docs.includeinbalcalc=-1 AND docs.transactiontype<>1 AND
docs.docdate<=$1
GROUP BY docacctransactions.accgroupid;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION aa(date) OWNER TO sa;
I am using WinXP SP 2 with PostgreSQL 8.3 with ODBC
Please help.
CPK
--
Keep your Environment clean and green.
^ permalink raw reply [nested|flat] 12+ messages in thread
* functions
@ 2008-08-02 10:58 C K <shreeseva.it@gmail.com>
parent: C K <shreeseva.it@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: C K @ 2008-08-02 10:58 UTC (permalink / raw)
To: pgsql-admin
Dear Friends,
I have written a function as follows. Here I need to get the sum of balance
before the user provided date. The below function works well, but returns
records as a set of values, rather I need individual columns. What to do?
CREATE OR REPLACE FUNCTION aa(date)
RETURNS SETOF record AS
$BODY$SELECT docacctransactions.accgroupid, Sum(docacctransactions.credit)
AS OSC, Sum(docacctransactions.debit) AS OSD
FROM docs INNER JOIN docacctransactions ON docs.docid =
docacctransactions.docid
WHERE docs.includeinbalcalc=-1 AND docs.transactiontype<>1 AND
docs.docdate<=$1
GROUP BY docacctransactions.accgroupid;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION aa(date) OWNER TO sa;
I am using WinXP SP 2 with PostgreSQL 8.3 with ODBC
Please help.
CPK
--
Keep your Environment clean and green.
--
Keep your Environment clean and green.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: functions
@ 2008-08-04 13:33 Lennin Caro <lennin.caro@yahoo.com>
parent: C K <shreeseva.it@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Lennin Caro @ 2008-08-04 13:33 UTC (permalink / raw)
To: pgsql-admin; C K <shreeseva.it@gmail.com>
create a type and return this
--- On Sat, 8/2/08, C K <shreeseva.it@gmail.com> wrote:
> From: C K <shreeseva.it@gmail.com>
> Subject: [ADMIN] functions
> To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Date: Saturday, August 2, 2008, 9:39 AM
> Dear Friends,
> I have written a function as follows. Here I need to get
> the sum of balance
> before the user provided date. The below function works
> well, but returns
> records as a set of values, rather I need individual
> columns. What to do?
> CREATE OR REPLACE FUNCTION aa(date)
> RETURNS SETOF record AS
> $BODY$SELECT docacctransactions.accgroupid,
> Sum(docacctransactions.credit)
> AS OSC, Sum(docacctransactions.debit) AS OSD
> FROM docs INNER JOIN docacctransactions ON docs.docid =
> docacctransactions.docid
> WHERE docs.includeinbalcalc=-1 AND
> docs.transactiontype<>1 AND
> docs.docdate<=$1
> GROUP BY docacctransactions.accgroupid;
> $BODY$
> LANGUAGE 'sql' VOLATILE
> COST 100
> ROWS 1000;
> ALTER FUNCTION aa(date) OWNER TO sa;
>
> I am using WinXP SP 2 with PostgreSQL 8.3 with ODBC
> Please help.
> CPK
> --
> Keep your Environment clean and green.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Functions
@ 2008-09-15 16:55 c k <shreeseva.learning@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: c k @ 2008-09-15 16:55 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-admin
Dear PG members,I want to know that does begin and end in plpgsql are
related to begin transactions and commit? if yes/no how? If I start to
execute a function of multiple statements does PG commits each transaction
within a function, or whole function as a transaction.
Also if first statement makes inserts/updates a row, does it automatically
available to that particular function execution only or to any other
instances of same function?
Please give the details.
In my system, function are extensively used and cab called by different
clients as ODBC, JDBC and others.
Thanks for help,
CPK
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Functions
@ 2008-09-15 17:51 Scott Marlowe <scott.marlowe@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Scott Marlowe @ 2008-09-15 17:51 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-admin
On Mon, Sep 15, 2008 at 10:55 AM, c k <shreeseva.learning@gmail.com> wrote:
> Dear PG members,
> I want to know that does begin and end in plpgsql are related to begin
> transactions and commit? if yes/no how?
No. a plpgsql function is a transaction in its entirety. If called
within a transaction it inherits the parent transaction.
>If I start to execute a function of
> multiple statements does PG commits each transaction within a function, or
> whole function as a transaction.
Whole thing, all or nothing.
> Also if first statement makes inserts/updates a row, does it automatically
> available to that particular function execution only or to any other
> instances of same function?
To that function only. Until commit no one else should see the change.
> Please give the details.
> In my system, function are extensively used and cab called by different
> clients as ODBC, JDBC and others.
How called isn't all that critical, unless you've set some kind of
autocommit flag or something.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Functions
@ 2008-09-15 17:53 c k <shreeseva.learning@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: c k @ 2008-09-15 17:53 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-general@postgresql.org; pgsql-admin
On Mon, Sep 15, 2008 at 11:22 PM, c k <shreeseva.learning@gmail.com> wrote:
> If I have a function having:begin
> insert into something ...
> select max(primary key) from something..
> end;
>
> does the second statement within a function can view the results after
> execution of first statement to get max(P.K.)?
>
> CPK
>
> On Mon, Sep 15, 2008 at 10:38 PM, Richard Huxton <dev@archonet.com> wrote:
>
>> c k wrote:
>> > Dear PG members,I want to know that does begin and end in plpgsql are
>> > related to begin transactions and commit? if yes/no how?
>>
>> No. All functions operate within a transaction.
>>
>> > If I start to
>> > execute a function of multiple statements does PG commits each
>> transaction
>> > within a function, or whole function as a transaction.
>>
>> You can have many statements (function calls) within a single
>> transaction. If you don't explicitly issue BEGIN ... COMMIT then each
>> statement is wrapped in its own transaction. Note that some client
>> libraries have "autocommit" options that do things their own way.
>>
>> > Also if first statement makes inserts/updates a row, does it
>> automatically
>> > available to that particular function execution only or to any other
>> > instances of same function?
>> > Please give the details.
>>
>> If you insert/update/delete one or more rows, they are visible outside
>> the function - it's all the same tables.
>>
>> > In my system, function are extensively used and cab called by different
>> > clients as ODBC, JDBC and others.
>>
>> There is no difference between a user-written function and a built-in
>> function in PostgreSQL as regards calling them or their effects. Some
>> clients might offer better support than others, but they should all let
>> you build "raw" sql.
>>
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Functions
@ 2008-09-15 17:57 Scott Marlowe <scott.marlowe@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2008-09-15 17:57 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-general@postgresql.org; pgsql-admin
On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva.learning@gmail.com> wrote:
>
>
> If I have a function having:
> begin
> insert into something ...
> select max(primary key) from something..
> end;
>
> does the second statement within a function can view the results after
> execution of first statement to get max(P.K.)?
Yep.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Functions
@ 2008-10-01 00:54 Rafael Domiciano <rafael.domiciano@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Rafael Domiciano @ 2008-10-01 00:54 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: c k <shreeseva.learning@gmail.com>; Richard Huxton <dev@archonet.com>; pgsql-general@postgresql.org; pgsql-admin
Hello,
If you want to get the value of a autogenerated column it's better to use
"RETURNING"
insert into something returning primary key...
Best Regards,
Rafael Domiciano
Postgres DBA
2008/9/15 Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva.learning@gmail.com>
> wrote:
> >
> >
> > If I have a function having:
> > begin
> > insert into something ...
> > select max(primary key) from something..
> > end;
> >
> > does the second statement within a function can view the results after
> > execution of first statement to get max(P.K.)?
>
> Yep.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-10-01 00:54 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-27 08:56 Functions Doron Baranes <doron.baranes@dbnet.co.il>
2006-11-27 09:33 ` imad <immaad@gmail.com>
2008-06-24 10:43 Functions C K <shreeseva.it@gmail.com>
2008-06-24 16:20 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-08-02 09:39 functions C K <shreeseva.it@gmail.com>
2008-08-02 10:58 ` functions C K <shreeseva.it@gmail.com>
2008-08-04 13:33 ` Re: functions Lennin Caro <lennin.caro@yahoo.com>
2008-09-15 16:55 Functions c k <shreeseva.learning@gmail.com>
2008-09-15 17:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-09-15 17:53 ` c k <shreeseva.learning@gmail.com>
2008-09-15 17:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-01 00:54 ` Rafael Domiciano <rafael.domiciano@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox