2

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.

Celeritas
  • 14,489
  • 36
  • 113
  • 194

2 Answers2

2

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

Community
  • 1
  • 1
ertx
  • 1,494
  • 2
  • 15
  • 21
  • Ok you're right I don't want to return void. What does 'LANGUAGE sql VOLATILE ROWS 1000; ' do? – Celeritas Oct 23 '12 at 08:08
  • Here's a manual about function volatility: http://www.postgresql.org/docs/8.2/static/xfunc-volatility.html As for Rows counter, it's optional, it limits the number of rows function returns. LANGUAGE defines the procedural language used for syntax. 'sql' means it'll work on probably all SQL servers. postgresql-specific is pl/pgsql, which is slightly slower, but has much more options. There's a link in the bottom of an answer about procedural languages. – ertx Oct 23 '12 at 08:12
  • What's the difference between stable and immutable? Of course someone can update the table but everything else would be the same, would this make it stable? – Celeritas Oct 23 '12 at 08:18
  • Immutable basicaly means that the return of the function will be defined when it's created and it will not change unless the function is modified. The manual covers the rest of information. If you have any further questions, that are unrelated to this one, you should try searching in StackOverflow, or create them as new onesi if documentation does not explain it clearly enough – ertx Oct 23 '12 at 08:23
1

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?

Sam Choukri
  • 1,874
  • 11
  • 17
  • Wouldn't the select statement display as usual? – Celeritas Oct 23 '12 at 07:59
  • In this particular case, you would get a type-mismatch error because you declared the function to return type VOID but the select statement will try to return the type of the field "form". – Sam Choukri Oct 23 '12 at 17:03