Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8FD71632754 for ; Mon, 21 Jun 2010 18:01:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 28421-03 for ; Mon, 21 Jun 2010 21:01:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 048236326C1 for ; Mon, 21 Jun 2010 18:01:29 -0300 (ADT) Received: by wwb13 with SMTP id 13so844899wwb.19 for ; Mon, 21 Jun 2010 14:01:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=3iHK841cCByE8S3sex3xpxqx8ygNJ9WeswQCaqQo5C8=; b=i5nBXq1qDHXoEVv1kyzhwqCxD0PWj+PNkTRr0e7JmO5guwwSHMd4BndnzY+CHPnFpv DbjBD7j+BPjkHJTSv20i/hO/n6wFyp2NE8p2yuMn7kE+l0+ct/CTUoW/FHRWRZoBkcxv D42CZRyGwY9M7gYhMeKldJIAf9sOg8cJOwYww= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=JXkSSCL625SlBPpiNqnQCDtJGxyLvIWZffthHDRjGxL0uXBgFz4eyqv0D/+GyL2rHt bBGYm7/TLtFKbVGS4KjlRn8dZHJlFvJekLK24Neqf2UVUgMJSfZN6tK7+eGoS0x6nuPf THOTaJCffoz7vYP06srJlpq4H/XIulJJ9TWSE= MIME-Version: 1.0 Received: by 10.216.90.130 with SMTP id e2mr650476wef.99.1277154087676; Mon, 21 Jun 2010 14:01:27 -0700 (PDT) Received: by 10.216.175.69 with HTTP; Mon, 21 Jun 2010 14:01:27 -0700 (PDT) In-Reply-To: References: Date: Mon, 21 Jun 2010 17:01:27 -0400 Message-ID: Subject: Re: running scripts like oracle sqlplus From: Brian Sherwood To: Steven Dahlin Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e6db2979b82d980489909b43 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/65 X-Sequence-Number: 34256 --0016e6db2979b82d980489909b43 Content-Type: text/plain; charset=ISO-8859-1 Have you tried nextval & currval? http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html Something like this: begin insert into user ( user_id, customer_id, create_user, update_user ) values ( nextval(user_seq), nextval(customer_seq), currval(user_seq), currval(user_seq) ); insert into customer ( customer_id, create_user, update_user ) values ( currval(customer_seq), currval(user_seq), currval(user_seq) ); commit; end; On Fri, Jun 18, 2010 at 6:24 PM, Steven Dahlin wrote: > I have been trying to figure out how I can run a pgsql script like I can > run a plsql script with oracle's sqlplus. Here is a sample script file for > what I want to run: > > declare > sysuserid integer := 0; > hwcustid integer := 0; > begin > > select nextval( 'user_seq' ) into sysuserid; > select nextval( 'customer_seq' ) into hwcustid; > > insert into user > ( > user_id, > customer_id, > create_user, > update_user > ) > values > ( > sysuserid, > hwcustid, > sysuserid, > sysuserid > ); > > insert into customer > ( > customer_id, > create_user, > update_user > ) > values > ( > hwcustid, > sysuserid, > sysuserid > ); > > commit; > > end; > > I try to run the script in psql and thru pgadmin and cannot seem to make > them work. I do not want to turn it into a function. I just want it to > execute the block in a fashion similar to Oracle'sqlplus running > @scriptfile.sql. > > Thanks > --0016e6db2979b82d980489909b43 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Have you tried nextval & currval?
http://www.postgresql.= org/docs/8.4/interactive/functions-sequence.html=A0

Something l= ike this:


begin

insert into user
(
=A0 user_id,
=A0 customer_id,
=A0 create_user,
=A0 update_user
)
values
(
=A0 nextval(user_seq),
=A0 nextval(customer_seq),=A0 currval(user_seq),
=A0 currval(user_seq)
);

insert into c= ustomer
(
=A0 customer_id,
=A0=20 create_user,
=A0 update_user
)
values
(
=A0 currval(customer= _seq),
=A0 currval(user_seq),
=A0 currval(user_seq)
);

commit;=A0

end;



<= br>
On Fri, Jun 18, 2010 at 6:24 PM, Steven D= ahlin <pgdb= .sldahlin@gmail.com> wrote:
I have been tryin= g to figure out how I can run a pgsql script like I can run a plsql script = with oracle's sqlplus.=A0 Here is a sample script file for what I want = to run:

declare
=A0 sysuserid integer :=3D 0;
=A0 hwcustid=A0 integer := =3D 0;
begin

select nextval( 'user_seq' )=A0into=A0 sysuserid;
s= elect nextval( 'customer_seq' )=A0into=A0 hwcustid;

insert i= nto user
(
=A0 user_id,
=A0 customer_id,
=A0 create_user,
= =A0 update_user
)
values
(
=A0 sysuserid,
=A0 hwcustid,
=A0 sysuserid,
= =A0 sysuserid
);

insert into customer
(
=A0 customer_id,=A0 create_user,
=A0 update_user
)
values
(
=A0 hwcustid,=A0 sysuserid,
=A0 sysuserid
);

commit;=A0

end;

I try to run the = script in psql and thru pgadmin and cannot seem to make them work.=A0 I do = not want to turn it into a function.=A0 I just want it to execute the block= in a fashion similar to Oracle'sqlplus running @scriptfile.sql.

Thanks

--0016e6db2979b82d980489909b43--