CODEWITHSUNDEEP
sqlfunctionpostgresqlparameters
Eduan Bekker
Eduan Bekker

Reputation: 431

Postgresql function per row

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!

Upvotes: 1

Views: 186

Answers (3)

Christian
Christian

Reputation: 7320

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).

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125474

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.

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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;

Upvotes: 3

Related Questions