CODEWITHSUNDEEP
sqlpostgresqluser-defined-functions
Celeritas
Celeritas

Reputation: 15053

creating a user defined function that doesn't return anything

I am trying to make a user defined function that returns nothing. I have the body: select form from bookings where id=$1 where $1 is the first and only argument the function takes.

I've tried

create function findBookgins(character varying(100)) returns void
select form from bookings where id=$1;

and I get an error at the select. I just want the table created to be displayed.

Upvotes: 2

Views: 18181

Answers (2)

Sam Choukri
Sam Choukri

Reputation: 1904

You have syntax errors in your function. You need to properly quote the body of the function. 

CREATE FUNCTION findBookgins(character varying(100)) RETURNS VOID AS
$$
  SELECT form FROM bookings WHERE id=$1;
$$
LANGUAGE SQL
;

I think the documentation on SQL-based functions will be particularly helpful to you.

By the way, I really can't believe you want this particular function to return nothing. What is the point of that?

Upvotes: 1

ertx
ertx

Reputation: 1544

Function call should look something like:

SELECT * FROM findBookgins('FooBar' :: character varying);

Or

SELECT findBookings('Foo');

Or 

Perform findBookings('Bar'); 
/* note this call will not return anything but 
   is only available when calling function from other functions */

Edit: Okay let's look at the problem step by step. First of all returning nothing: Here's a perfectly good explanation about how function return works in postgresql

Next how to define function (since your code is pure sql, i'm guessing you need a function in sql aswell):

CREATE OR REPLACE FUNCTION findBookgins(prm_booking character varying(100))
  RETURNS SETOF bookings AS
    'SELECT * FROM bookings WHERE id = $1';
  LANGUAGE sql VOLATILE
  ROWS 1000;

This function should return all the bookings that match your given criteria as so:

SELECT * FROM findBookings('15');

will return all bookings with id 15.

I'm assuming that's what you're trying to do because otherwise your function wouldn't do anything, to use loops you need plpgsql functions

More about plpgsql procedural language syntax here

Upvotes: 2

Related Questions