X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0C076D7D47 for ; Fri, 11 Nov 2005 06:24:43 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57433-01 for ; Fri, 11 Nov 2005 10:24:40 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from loki.globexplorer.com (loki.globexplorer.com [12.25.176.46]) by svr1.postgresql.org (Postfix) with ESMTP id 5013ED6D4E for ; Fri, 11 Nov 2005 06:24:41 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Subject: Re: Como ejecutar una funcion insert en plpgsql.... Date: Fri, 11 Nov 2005 02:24:37 -0800 Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BBB9C@loki.wc.globexplorer.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Como ejecutar una funcion insert en plpgsql.... Thread-Index: AcXmAmHW6nCpKnS4To2hq/t3tDZJCQApNRtO From: "Gregory S. Williamson" To: "Fernando Garcia" Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/101 X-Sequence-Number: 23352 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=3D# 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.=20 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 " =3D " 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.=20 sp_retval =3D (SELECT 1 FROM users WHERE user_name =3D p_u_name = AND user_email =3D p_u_email AND user_pwd =3D p_u_pwd); RAISE NOTICE ''got back test val of %'', sp_retval; IF (sp_retval >=3D 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);=20 END IF; END; ' LANGUAGE 'plpgsql'; COMMIT; And when I test it: gex_runtime=3D# select * from insert_a_user('Joe Major', = 'joe@someplace.com', 'apassWord?'); NOTICE: doing insert for Joe Major NOTICE: got back test val of NOTICE: done with insert insert_a_user --------------- 0 (1 row) And the row is in the table: gex_runtime=3D# select * from users; user_name | user_email | user_pwd -----------+-------------------+------------ Joe Major | joe@someplace.com | apassWord? (1 row) Try it again -- should fail: gex_runtime=3D# select * from insert_a_user('Joe Major', = 'joe@someplace.com', 'apassWord?'); NOTICE: doing insert user for Joe Major NOTICE: got back test val of 1 insert_a_user --------------- 1 (1 row) gex_runtime=3D# select * from users; user_name | user_email | user_pwd -----------+-------------------+------------ Joe Major | joe@someplace.com | apassWord? (1 row) And sure enough -- just the original record. Add another user: gex_runtime=3D# select * from insert_a_user('Mary Contrary', = 'mc@yaboo.com', 'only4me!'); NOTICE: doing insert user for Mary Contrary NOTICE: got back test val of NOTICE: done with insert insert_a_user --------------- 0 (1 row) gex_runtime=3D# select * from users; user_name | user_email | user_pwd ---------------+-------------------+------------ Joe Major | joe@someplace.com | apassWord? Mary Contrary | mc@yaboo.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@gmail.com] Sent: Thu 11/10/2005 6:23 AM To: Gregory S. Williamson Cc:=09 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 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@gmail.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 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" =3D ItemID > > ItemID =3D 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@postgresql.org on behalf of Fernando Garcia > > Sent: Wed 11/9/2005 6:22 AM > > To: pgsql-sql@postgresql.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???? > > > > > > > > > > > > > > > > >=20 > > > > !DSPAM:4373535f235641799941469!