[SQL] logging a psql script

[Ken Kline]

Hello,
   I would like my psql script to log everything that it does.
I set the following

\set ECHO all
\o foo.txt
\qecho

some sql, some ddl, etc...

\o


But foo.txt only contains

DROP
DROP
DROP
CREATE
CREATE
CREATE

I want it to contain everything that I see on the screen, what am I
missing?

Thanks

Ken

[SQL] logging a script

[Ken Kline]

Hello,
   I would like my psql script to log everything that it does.
I set the following

\set ECHO all
\o foo.txt
\qecho

some sql, some ddl, etc...

\o


But foo.txt only contains

DROP
DROP
DROP
CREATE
CREATE
CREATE

I want it to contain everything that I see on the screen, what am I
missing?

Thanks

Ken

[SQL] greetings

[Ken Kline]

I have just joined the list a few days ago and am trying quite hard
to come up to speed with pgsql but i find documentaion frustratiing.
I think maybe it;s just a matter of finding things that are of the
correct
scope.  I've been an Oracle developer for over 6 years so often I
know what it is I want to do but something is just a little different.
If there are others on the list that learned in Oracle then pgsql
please tell me what  you think are the best resources.

Recently I did a google search on the key words "postgresql cursor loop"

the example below is all  I could come up with but it doesn't seem
to work is this for an older version or am I just overlooking
something simple?

thanks

Ken

 DECLARE emp_cursor CURSOR FOR
 SELECT Salary, Title, Start, Stop
 FROM Employee;
 OPEN emp_cursor;
 loop:
 FETCH emp_cursor INTO :salary, :start, :stop;
 if no-data returned then goto finished;
 find position in linked list to insert this information;
 goto loop;
 finished:
 CLOSE emp_cursor;

Re: [SQL] greetings

[Ken Kline]

it is to be server side code
the code I gave you was merely an example
of a cursor that I found when I did a search...
http://www.armed.net/how/pg001676.htm

orginally what I wanted to do was this:

INSERT INTO pledge_classes (semester, year)
SELECT distinct pseason, pyear from load_bros
WHERE  pyear is not null
ANDpseason is not null
order by pyear, pseason;

however pgsql does not allow order by in an INSERT-SELECT statement
so i thought maybe I could do something like this:


DECLARE
CURSOR get_rows AS
SELECT DISTINCT pseason, pyear FROM load_members
WHERE pyear IS NOT NULL
AND pseason IS NOT NULL
ORDER BY pyear, pseason;
BEGIN
FOR rec IN get rows LOOP
INSERT INTO pledge_classes (semester, year)
VALUES
(rec.pseason, rec.pyear);
END LOOP;
COMMIT;
END;
/


Well, all the code I just showed you works in orcacle but pgsql is a
little different
and even though the book has an example of a cursor
http://www.postgresql.org/docs/aw_pgsql_book/node142.html
it does not explain before hand
1) the format of an anoymous block
2) how to loop a cursor
3) how to reference columns froma cursor row (ie rec.column_name)

thanks

Ken


Tom Lane wrote:

> Ian Lance Taylor <[EMAIL PROTECTED]> writes:
> > PL/pgSQL does not support cursors.  It also does not support goto.
>
> The context is pretty unclear here, but perhaps he needs ecpg not
> plpgsql ... is this to be client- or server-side code?
>
> regards, tom lane

[SQL] conversion

[Ken Kline]

Hello,
another brain twister, at least for me...

i have a table of varchar and one of the values I want
to insert into another table, one of the columns is
defined as INTEGER in destination table, column...
and none of these statements seem to work

 INSERT INTO pledge_classes (semester, year)
 SELECT pseason, to_number('pyear','') from temp;

 INSERT INTO pledge_classes (semester, year)
 SELECT pseason, pyear::integer from temp;

 INSERT INTO pledge_classes (semester, year)
 SELECT pseason, pyear::numeric(4) from temp;

Re: [SQL] conversion

[Ken Kline]

follow up
actually the destination column is defined
as a numeric(4)

the following are the statements again with there error messages:

SELECT pseason, to_number(pyear,'') from temp;
ERROR: Bad numeric input format  '  '

 SELECT pyear::int from temp;
ERROR: Cannot cast type 'varchar' to 'int4'




Ken Kline wrote:

> Hello,
> another brain twister, at least for me...
> i have a table of varchar and one of the values I want
> to insert into another table, one of the columns is
> defined as INTEGER in destination table, column...
> and none of these statements seem to work
>
>  INSERT INTO pledge_classes (semester, year)
>  SELECT pseason, to_number('pyear','') from temp;
>
>  INSERT INTO pledge_classes (semester, year)
>  SELECT pseason, pyear::integer from temp;
>
>  INSERT INTO pledge_classes (semester, year)
>  SELECT pseason, pyear::numeric(4) from temp;

Re: [SQL] conversion

[Ken Kline]

here you go, thanks in advance, ken

Table "temp"
 Attribute |Type | Modifier
---+-+--
 pseason   | varchar(15) |
 pyear | varchar(5)  |

adx=# \d pledge+ _classe4s  s
   Table "pledge_classes"
 Attribute | Type | Modifier
---+--+--
 pc_id | integer  | not null default nextval('pc_seq'::text)
 semester  | varchar(6)   |
 year  | numeric(4,0) |
 pc_letter | varchar(20)  |
 pc_name   | varchar(50)  |
Index: pc_pk

adx=# \q
bash-2.04$
Script done on Mon Feb 26 11:42:35 2001


Christopher Sawtell wrote:

> On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
> > follow up
> > actually the destination column is defined
> > as a numeric(4)
>
> Could you to a \d on each of the tables and tell us the results.
>
> [ ... ]
>
> --
> Sincerely etc.,
>
>  NAME   Christopher Sawtell
>  CELL PHONE 021 257 4451
>  ICQ UIN45863470
>  EMAIL  csawtell @ xtra . co . nz
>  CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
>
>  -->> Please refrain from using HTML or WORD attachments in e-mails to me
> <<--

Re: [SQL] Weird NOT IN effect with NULL values

[Ken Kline]

this is kind of weird but it is how it works.
You cannot use equality for null...
Null does not equal Null
Null means no value, since it's not a value
it can't equal anything another no value.

SELECT name
FROM customer
WHERE customer_id NOT IN
(
SELECT customer_id
FROM salesorder
)
and customer_id is not null;

should work

Ken


Frank Joerdens wrote:

> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
>
> I am using 7.1 beta 4.
>
> Regards, Frank

[SQL] perl dbd

[Ken Kline]

my apologies if this is not the coreect list
but I cannot seem to install the
package DBD-Pg-0.73-1.i386.rpm

it complains that it needs libpq.so.1

i have the following installed from
a source package rebuild:

postgresql-7.0.3-2
..server
..devel
..perl
..tk
..odbc
..tcl

thanks as always

Ken



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl