[SQL] plpgsql, double quoted column names containing spaces/hyphens
Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS VStudio2003 C++ -> ADO -> ODBC 7.03.01.00. In plpgsql, I can access columns with a name containing capital letters by enclosing the name in double quotes: tbl."ColName" However, if the quoted name contains spaces or hyphens I get an error message. It is surprising that this does not work in plpgsql when it does with sql. Thus, the following line produces an error (unterminated ") in plpgsql: IF OLD."-person-initials" ... THEN However, I discovered that rewriting using EXECUTE does work: DECLARE curs1 refcursor; personinitials int4; ... OPEN curs1 FOR EXECUTE ''SELECT "-person-initials" AS pers FROM "''||TG_RELNAME||''" WHERE id=''||OLD.id; FETCH curs1 INTO personinitials; CLOSE curs1; IF personinitials ... THEN Could I have solved this problem in an easier way, by using some kind of escape codes in the example producing the error (IF OLD."-person-initials" ... THEN)? Thanks, KP ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] length of recordset read through a cursor
After declaring a cursor, one way of obtaining the length of the resultset is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn" where nn is the length of the resultset. (A negative MOVE can then be used to allow starting to fetch records from the beginning of the resultset.) Is there another, possibly faster way? Thanks, K.P.Lehre ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] length of recordset read through a cursor
>> After declaring a cursor, one way of obtaining the length of the >resultset >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a >"MOVE nn" >> where nn is the length of the resultset. (A negative MOVE can then be >used >> to allow starting to fetch records from the beginning of the >resultset.) >> >> Is there another, possibly faster way? >> >Looks like you're using libpq (because you mention PQcmdStatus), >then after declaring a cursor and FETCH ALL, try > >1.3.4. Retrieving SELECT Result Information > >PQntuples Returns the number of tuples (rows) in the query result. > >int PQntuples(const PGresult *res); > >I'm not exactly sure what you're trying to achieve or going to do, >so if I misunderstood you, ask again. > >Regards, Christoph Thanks for your reply. What I'm trying to do is the following: I want to browse through a view containing more than 1 records. To avoid slowing things down too much, I would like my client program to receive (through the network) only the records that are to be displayed on the screen. I believe I could do this by declaring a cursor and then fetching the parts of the resultset I need. It would be useful to know the size of the resultset immediately after the cursor has been declared. How do I get this information? I could of course fetch all of the resultset, but that is what I am trying to avoid. Shouldn't it be quicker to perform a move through the set than fetching it? I found that moving zero records results in a move to the end of the resultset, with a command status returning the number of records moved. Although I expected this method to take less time than a fetch (does it?), I was wondering if there might be another way to get the size of the resultset that can be fetched through the declared cursor. KP ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] select
I have a table with fields: id A B C D E F where id is an int4 primary key. In this table there is information like: 1 a1 b1 xxx xxx 2 a1 b1 xxx xxx xxx xxx 3 a2 b2 xxx xxx xxx xxx 4 a2 b2 xxx xxx xxx xxx 5 a3 b3 xxx xxx xxx 6 a3 b3 xxx xxx xxx xxx 7 a3 b3 xxx xxx xxx xxx 8 a4 b4 xxx xxx xxx xxx 9 a1 b1 xxx 10 a3 b3 xxx 11 a1 b3 xxx where xxx represents any information. My problem is: I only want to select one of the records which have the same combination of information in fields A and B, and that should be the record with the lowest id. Thus, the resultset should be: 1 a1 b1 xxx xxx 3 a2 b2 xxx xxx xxx xxx 5 a3 b3 xxx xxx xxx 8 a4 b4 xxx xxx xxx xxx 11 a1 b3 xxx Importantly, I might not want the selected records listed in order of increasing id. They might be sorted according to the data in e.g. the C field. Suggestions/comments greatly appreciated. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
