Postgresql function per row

Question

I am currently learning postgresql and have a noob question. If I have a simple table like this:

CREATE TABLE rectangle
(
    width int,
    length int
);

And a function like this:

CREATE OR REPLACE FUNCTION Area()
RETURNS int AS $area$
declare
    area int;
BEGIN
   SELECT (width*length)  into area FROM rectangle;
   return area ;
END;
$area$ LANGUAGE plpgsql;

And I call it like this:

select width, length, area() from rectangle;

I get results like this:

width  |  length  |  area
-------------------------
2      |     3    |    6
4      |     3    |    6
5      |     2    |    6

Which shows that the area function is working but it is only using the first entry in the table and not the corresponding row. What am I doing wrong. Thanks in advance!

You have assumed the function will "know" it is in a row, and therefore use that row. Not true. The function is independent of the row, and you must "pass" values from the row into that function. While this is probably just a simple example, you don't need to use a function for this and you should just use (width*length) directly in the query — Paul Maxwell
– Paul Maxwell, Commented Aug 8, 2014 at 9:21
I see what I did wrong. So in general it is best to send in a pk for the function and add a WHERE — Eduan Bekker
– Eduan Bekker, Commented Aug 8, 2014 at 9:28
why did you use select width,length,(length*width) as area from rectangle >> ?? this is not a good situation to implement a Function — Vivek S.
– Vivek S., Commented Aug 8, 2014 at 9:32
"so in general ..." mmm, not really, the sample functions below both "abstract" the area calculation from any physical implementation of length or width, so the function could be used in any relevant situation. If you base a function on a PK then the function can only be used in one situation. Also note that in the sample functions below they do not need to use select, because you pass the length and the width which have already been selected. Passing a pk would require another select, which is not great. — Paul Maxwell
– Paul Maxwell, Commented Aug 8, 2014 at 9:33

Giannis Paraskevopoulos · Accepted Answer · 2014-08-08 09:12:29Z

3

CREATE OR REPLACE FUNCTION Area(integer, integer)
RETURNS int AS $area$
declare
    area int;
BEGIN
   SELECT ($1 * $2)  into area ;
   return area ;
END;
$area$ LANGUAGE plpgsql;

And then on your query:

select width, length, area(width, length) from rectangle;

answered Aug 8, 2014 at 9:12

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2014-08-08 09:13:41Z

1

In your function you are getting the values from one undetermined row from the table. It is necessary to pass the parameters to the function

create or replace function area (
    width int, length int
) returns int as $area$

   select width * length;

$area$ language sql;

It can be plain SQL.

answered Aug 8, 2014 at 9:13

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Christian · Accepted Answer · 2014-08-08 15:03:09Z

0

This is much more efficient:

CREATE OR REPLACE FUNCTION Area(integer, integer)
RETURNS int AS 
$body$
BEGIN
   return ($1 * $2) ;
END;
$body$ 
LANGUAGE plpgsql 
IMMUTABLE;

Reasons:

No SQL evaluating;
An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE. (see more).

answered Aug 8, 2014 at 15:03

Christian

7,3509 gold badges58 silver badges81 bronze badges

Collectives™ on Stack Overflow

Postgresql function per row

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related