Re: Como ejecutar una funcion insert en plpgsql....

Fernando --

It is best to always copy to the list -- more eyes means a better chance of help.

You can use native SQL to define a database (once connected to the "template1" database issue a "CREATE DATABASE foo;" command), or use one of the pgAdmin option, IIRC -- I don't have it installed on this computer and haven't used it recently. I don't remember if they have a GUI for creating tables -- I almost always use SQL directly.

In answer to you question about creating a function to do inserts, I have a very simple test case.

I create a table:
gex_runtime=# create table users (
user_name text, -- postgres' easiest string variable is text
user_email text,
user_pwd text);

This is obviously quite simple -- no index or serial ids, etc., which I almost always use, nor a timestamp or any other goodies. But enough to show the idea I hope.

Then I create a simple function in the plpgsql language. After you create the database you need to run a "createlang plpgsql" at a command prompt; pgAdmin surely has a way of creating language support in a database but I don't remember what that method is.

Remove/comment out the NOTICE lines when you are done debugging (? depulgar ? IIRC from my days in Nicaragua long ago) as they will always appear in the logs etc.

This function doesn't do any sanity checking, but you could make it check for obvious errors like missing data; a NULL value passed to this function will cause undesirable results because of a " = " test that would need to be able to be an IS NULL test.

The only check this function makes is to see if we already have the name, email and password; if we do the function returns an integer value of 1; if it suceeds it will return a 0.

Remember that you can't put a transaction into a function (well, not in postgres 7.4, which is where this example comes from), although you can use savepoints I think in the latest releasesl check the manuals for guidance on transactions and functions for details.

BEGIN;
CREATE OR REPLACE FUNCTION insert_a_user(TEXT,TEXT,TEXT)
RETURNS INTEGER AS '
DECLARE p_u_name ALIAS FOR $1;
p_u_email ALIAS FOR $2;
p_u_pwd ALIAS FOR $3;
sp_retval INTEGER;
BEGIN
RAISE NOTICE ''doing insert user for %'', p_u_name;
-- check here for bad data, etc.
sp_retval = (SELECT 1 FROM users WHERE user_name = p_u_name AND user_email = p_u_email AND user_pwd = p_u_pwd);
RAISE NOTICE ''got back test val of %'', sp_retval;
IF (sp_retval >= 1) THEN
RETURN(sp_retval);
ELSE
INSERT INTO users VALUES(p_u_name, p_u_email, p_u_pwd);
RAISE NOTICE ''done with insert'';
RETURN(0);
END IF;
END;
' LANGUAGE 'plpgsql';
COMMIT;

And when I test it:

gex_runtime=# select * from insert_a_user('Joe Major', 'joe(at)someplace(dot)com', 'apassWord?');
NOTICE: doing insert for Joe Major
NOTICE: got back test val of <NULL>
NOTICE: done with insert
insert_a_user
---------------
0
(1 row)

And the row is in the table:

gex_runtime=# select * from users;
user_name | user_email | user_pwd
-----------+-------------------+------------
Joe Major | joe(at)someplace(dot)com | apassWord?
(1 row)

Try it again -- should fail:

gex_runtime=# select * from insert_a_user('Joe Major', 'joe(at)someplace(dot)com', 'apassWord?');
NOTICE: doing insert user for Joe Major
NOTICE: got back test val of 1
insert_a_user
---------------
1
(1 row)

gex_runtime=# select * from users;
user_name | user_email | user_pwd
-----------+-------------------+------------
Joe Major | joe(at)someplace(dot)com | apassWord?
(1 row)

And sure enough -- just the original record.

Add another user:

gex_runtime=# select * from insert_a_user('Mary Contrary', 'mc(at)yaboo(dot)com', 'only4me!');
NOTICE: doing insert user for Mary Contrary
NOTICE: got back test val of <NULL>
NOTICE: done with insert
insert_a_user
---------------
0
(1 row)

gex_runtime=# select * from users;
user_name | user_email | user_pwd
---------------+-------------------+------------
Joe Major | joe(at)someplace(dot)com | apassWord?
Mary Contrary | mc(at)yaboo(dot)com | only4me!
(2 rows)

HTH,

Greg W.

ps be sure to send questions, etc. to the list as a whole so that more people have a chance to see the question and answer, and because any one individual might be gone for a while.

-----Original Message-----
From: Fernando Garcia [mailto:cubafer(at)gmail(dot)com]
Sent: Thu 11/10/2005 6:23 AM
To: Gregory S. Williamson
Cc:
Subject: Re: [SQL] Como ejecutar una funcion insert en plpgsql....
ok man dont worry I use postgresql 8.0.3...... other thing, I have a
postgresql DataBase in sql archive, how can i create in pgadmin III a data
base with this struct of table, column, etc. See i need charge a sql file in
a new pgsql database.
Thank a lot.......and rip in peace

On 11/10/05, Gregory S. Williamson <gsw(at)globexplorer(dot)com> wrote:
>
> Fernando --
>
> I have forwarded your message to the postgres SQL list where it started --
> it is good practice to "cc" the list in general so that others can
> contribute / learn.
>
> If you could provide the version of postgres you are using that might
> help, as there differences between 7.x and 8.x that might matter.
>
> I am at the very end of my day and too tired to be coherent -- perhaps
> someone else can provide an elegant snippet of code ?
>
> If there's no response in the next few hours I'll give it try.
>
> ZzzzZzzzzZzzz
>
> G
>
>
> -----Original Message-----
> From: Fernando Garcia [mailto:cubafer(at)gmail(dot)com]
> Sent: Thu 11/10/2005 5:23 AM
> To: Gregory S. Williamson
> Cc:
> Subject: Re: [SQL] Como ejecutar una funcion insert en plpgsql....
> thanks very much, jeje, my english its very rusry...
> i try explain to you what i can to do in the first question.....remember I
> try!!!!!
> Well, I have one function what insert one record in one tabe (EJ:
> adduser(name,email,password))
> How can I execute this function to prove this insertion......???
> Welllllll, you understand somethin in muy very bad english!!!
> OK Thanks anywere..
>
>
> On 11/9/05, Gregory S. Williamson <gsw(at)globexplorer(dot)com> wrote:
> >
> >
> > Fernando --
> >
> > I am not sure about the first question -- my spansih is rusry.
> >
> > postgres does force all column, table and schema names (I think) to
> lower
> > case (there have been recent long discussions about this on this list
> IIRC).
> > If you want to preserve case put the column name in double quotes:
> > "ItemID" = ItemID
> > ItemID = itemid
> >
> > This applies both to the creation of a table and when referring to that
> > table's columns.
> >
> > HTH,
> >
> > Greg Williamson
> > DBA
> > GlobeXplorer LLC
> >
> > -----Original Message-----
> > From: pgsql-sql-owner(at)postgresql(dot)org on behalf of Fernando Garcia
> > Sent: Wed 11/9/2005 6:22 AM
> > To: pgsql-sql(at)postgresql(dot)org
> > Cc:
> > Subject: [SQL] Como ejecutar una funcion insert en plpgsql....
> > Hola a todo... necesito ejecutar una consulta Insert que realice en
> pgsql
> > para ver si inserta correctamente en la tabla asociada, pero con execute
> > me
> > da un error....alguien me puede decir como lo hago....
> > yo trabajo con el editor postgresql manager pro..
> > Gracias...
> > Ahh otra cosa: en otra consulta que realice pero que es un select al
> > principio me daba un error porque no encontraba la columna "ItemID" la
> > cual
> > porsupuesto existia, entonces a la columna le quite las mayusculas de su
> > nombre quedando "itemid" y ya no me salio el error.....en postgres los
> > nombres de tablas y columnas deben ser siempre con minuscula????
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
>

!DSPAM:4373535f235641799941469!