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 tab= le , 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 postgresq= l . i know how to do it in MS-SQL , i also have that code .if you dont unde= rstand what am trying to say . i can post sql code .thanks

<= /div>

--1458340343-1666219641-1326877842=:18699-- From pavel.stehule@gmail.com Wed Jan 18 09:26:24 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 081FE86A549 for ; Wed, 18 Jan 2012 05:26:24 -0400 (AST) Received: from mail-qw0-f53.google.com ([209.85.216.53]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RnRmf-0004j4-VW for pgsql-sql@postgresql.org; Wed, 18 Jan 2012 09:26:23 +0000 Received: by qabg24 with SMTP id g24so50580qab.19 for ; Wed, 18 Jan 2012 01:26:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=gVtgsT8zRxMihsLlrepJKto3kJMjqepF950XcFUIJGo=; b=L3PBy4kiU08wyysBxABjzIshOIaEvbQhypqpyzKK2YaS3fqnZJFsk0FN/AwzBktJQ9 8Jwps/dNnfFOCDiCVr1cdBSv7ALrp8Rzax9SKb6knAGNZ/aAOi5ZJQ8wb8wJ5PoxCSkJ p65VUlgAFkxKM1eO9lbS35TNXbvczXYqo3R6I= Received: by 10.224.216.74 with SMTP id hh10mr20549965qab.45.1326878768481; Wed, 18 Jan 2012 01:26:08 -0800 (PST) MIME-Version: 1.0 Received: by 10.229.248.70 with HTTP; Wed, 18 Jan 2012 01:25:27 -0800 (PST) In-Reply-To: <1326877842.18699.YahooMailNeo@web121606.mail.ne1.yahoo.com> References: <1326877842.18699.YahooMailNeo@web121606.mail.ne1.yahoo.com> From: Pavel Stehule Date: Wed, 18 Jan 2012 10:25:27 +0100 Message-ID: Subject: Re: PostgreSQL Function To: Rehan Saleem Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=20cf300514a2a7c44404b6ca091f X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201201/83 X-Sequence-Number: 36254 --20cf300514a2a7c44404b6ca091f Content-Type: text/plain; charset=UTF-8 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 --20cf300514a2a7c44404b6ca091f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello

2012/1/18 Rehan Saleem <pk_rehan@yahoo.com><= /span>

hi=C2=A0
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 , becaus= e 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=C2=A0post=C2=A0sql code .tha= nks

CREATE TABLE users(id serial PRIMARY KEY, first_name varchar(10), last_na= me varchar(10));

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

postgres=3D# select new_user('pavel','stehule&= #39;);
=C2=A0new_user
----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1
(1 row)
postgres=3D# select new_user('pavel','very long text');ERROR:=C2=A0 value too long for type character varying(10)
CONTEXT:=C2= =A0 SQL statement "INSERT INTO users(first_name, last_name) VALUES(lna= me, fname) RETURNING id"
PL/pgSQL function "new_user" line 8 at SQL statement
postgres= =3D# select new_user('pavel','nemo');
ERROR:=C2=A0 bad l= ast_name: "nemo"
postgres=3D#

Regards

Pavel Ste= hule
--20cf300514a2a7c44404b6ca091f--