[SQL] Postgresql Function

[Rehan Saleem]

hi 
i want to create a function in postgresql that take input for columns from user 
for example first_name , last_name, addres. and will put them into table , and 
i also want to use exception if user enters wrong data. will some one help me 
how can i create thats function , because i am new to postgresql . i know how 
to do it in MS-SQL , i also have that code .if you dont understand what am 
trying to say . i can post sql code .thanks
REHAN SALEEM  

[SQL] PostgreSQL Function

[Rehan Saleem]

hi 
i want to create a function in postgresql that take input for columns from user 
for example first_name , last_name, addres. and will put them into table , and 
i also want to use exception if user enters wrong data. will some one help me 
how can i create thats function , because i am new to postgresql . i know how 
to do it in MS-SQL , i also have that code .if you dont understand what am 
trying to say . i can post sql code .thanks 

Re: [SQL] PostgreSQL Function

[Pavel Stehule]

Hello

2012/1/18 Rehan Saleem 

>  hi
> i want to create a function in postgresql that take input for columns from
> user for example first_name , last_name, addres. and will put them into
> table , and i also want to use exception if user enters wrong data. will
> some one help me how can i create thats function , because i am new to
> postgresql . i know how to do it in MS-SQL , i also have that code .if you
> dont understand what am trying to say . i can post 
> sql
> code .thanks
>
>
CREATE TABLE users(id serial PRIMARY KEY, first_name varchar(10), last_name
varchar(10));

CREATE OR REPLACE FUNCTION new_user(fname varchar, lname varchar)
RETURNS int AS $$
DECLARE r int;
BEGIN
  -- custom exception -- lname cannot be empty or NEMO
  IF trim(lname) = '' OR lower(lname) = 'nemo' THEN
RAISE EXCEPTION 'bad last_name: "%"', lname;
  END IF;
  INSERT INTO users(first_name, last_name) VALUES(lname, fname) RETURNING
id INTO r;
  RETURN r;
END;
$$ LANGUAGE plpgsql;

postgres=# select new_user('pavel','stehule');
 new_user
--
1
(1 row)

postgres=# select new_user('pavel','very long text');
ERROR:  value too long for type character varying(10)
CONTEXT:  SQL statement "INSERT INTO users(first_name, last_name)
VALUES(lname, fname) RETURNING id"
PL/pgSQL function "new_user" line 8 at SQL statement
postgres=# select new_user('pavel','nemo');
ERROR:  bad last_name: "nemo"
postgres=#

Regards

Pavel Stehule

[SQL] date range to set of dates expansion

[Gary Stainburn]

Hi,

How can I expand a date range in a table to a set of date records?

I have a table of availabilities thus:

  Column   |Type | Modifiers
---+-+
 aid   | integer | not null default 
nextval('availability_aid_seq'::regclass)
 asid  | integer | not null
 asdate| date| not null
 afdate| date| not null
 adays | integer |
 acomments | text|


asdate is the start date
afdate is the finish date

How can I expand this to a set of

  Column   |Type | Modifiers
---+-+
 aid   | integer | not null 
 asid  | integer | not null
 adate| date| not null
 acomments | text|

i.e.

 aid | asid |   asdate   |   afdate   | adays |  acomments
-+--+++---+
  12 |1 | 2007-08-11 | 2007-08-12 | 1 | Early finish Sunday

Becomes

 aid | asid |   asdate   |  acomments
-+--++
  12 |1 | 2007-08-11 | Early finish Sunday
  12 |1 | 2007-08-12 | Early finish Sunday

I have a function date_range to return a set of dates, but so far I can't get 
a valid view to work.

Also, is there a better method?

CREATE FUNCTION date_range(fdate date, tdate date) RETURNS SETOF date
AS $$
DECLARE
  wdate date;
BEGIN
  return next fdate;
  wdate:=fdate+1;
  while wdate <= tdate LOOP
return next wdate;
wdate:=wdate+1;
  end LOOP;
  return;
END;
$$
LANGUAGE plpgsql;

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql