CODEWITHSUNDEEP
functionpostgresqlvariablesplpgsql
Zapnologica
Zapnologica

Reputation: 22556

PostgreSQL function, need to get variables inside the function

I need some help with defining a Function in postgreSQL:

here are my table definitions:

CREATE TABLE venue (
   id INTEGER DEFAULT NEXTVAL('venue_id_seq')
 , building_code building_code
 , floorNo int
 , roomNo int
 , width int
 , length int
);

I need to create a function related to that table that gives me the Floor area.Here is what I have so far:

CREATE FUNCTION floorArea(id int) RETURNS int AS '
SELECT  venue.width * venue.length AS result ;
' LANGUAGE SQL;

I don't know how to possibly make variables etc.
I want something along the lines of:

var width=  Select width from venue where id=$1;
var length=  Select length from venue where id=$1;
return width * length;

Upvotes: 1

Views: 125

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

There are no variables inside an SQL function. You need to use PL/pgSQL or another procedural language for that.

With plpgsql it could look like this:

CREATE FUNCTION floor_area(_id int)
  RETURNS int AS
$func$
DECLARE
   _width   int;
   _length  int;
BEGIN

SELECT INTO _width, _length
             width,  length
FROM   venue
WHERE  id = _id;

RETURN width * length;

END
$func$ LANGUAGE plpgsql;

Consider what I wrote about parameter names in my other answer.

But a simple task like this should be solved with plain SQL without variables like @Roman already supplied.

Upvotes: 2

roman
roman

Reputation: 117380

I think you can do something like

CREATE FUNCTION floorArea(idp int)
RETURNS int
as
$$
    select width * length from venue as v where v.id = idp
$$ LANGUAGE SQL;

sql fiddle demo

Upvotes: 1

Related Questions