Re: [SQL] puzzled by SELECT INTO
Rodrigo De León wrote:
On 10/30/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote:
Wreird enough to me, need some advice plz!
CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
DECLARE
RETURNVALUE VARCHAR;
BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
SELECT WORD
FROM WORDS WHERE PAGE_ID=$1
AND WORD_POSITION = ANY ($2)
), ' ') INTO RETURNVALUE;
RETURN RETURNVALUE;
END;
$$ LANGUAGE PLPGSQL;
SELECT READ_WORDS(99466, '{2994,2995,2996}');
See:
http://www.postgresql.org/docs/8.2/static/arrays.html
Mmmm, yes, that make perfect sense. I did 'resolve' the previous
situation by using EXECUTE, i will try your solution now.
Gracias Rodrigo.
Gerardo
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Returning the total number of rows as a separate column when using limit
Hi. AFAICS the information about the *total* number of rows is in the "result" somehow. When I execute a "limit 1" query with EXPLAIN ANALYZE, I se the total number of columns in "rows=200819", so the information is there. andreak=# EXPLAIN ANALYZE select p.id from onp_crm_person p order by p.created DESC limit 1; QUERY PLAN --- Limit (cost=0.00..0.04 rows=1 width=12) (actual time=0.046..0.048 rows=1 loops=1) -> Index Scan Backward using origo_person_created_idx on onp_crm_person p (cost=0.00..8396.45 rows=200819 width=12) (actual time=0.041..0.041 rows=1 loops=1) Total runtime: 0.104 ms (3 rows) Is it possible to use some sort of "magic" function to get this number out as a separate column? And is this number accurate? It has to be the same as running a separate "count(*)"-query to count the totals, which is exactly what I'm trying to avoid. Oracle has a special rownum and over() which can be used to accomplish this: SELECT tmp.*, max(rownum) over() as total_count FROM (subquery) tmp Does PG have any equivalent way? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Backup Database
I have two Postgres databases (7.4 and 8.24) within the same network on LINUX platforms. Data sources go to both databases. I'd like to set up one as a near real-time backup to the operational database. Is it possible to incorporate a trigger upon update (or insert) on the operational 8.24 database to initiate an update (or insert) on the backup 7.4 database? How do most people set up a near real-time backup database? Perhaps there is something else that acts like a trigger. Any suggestions would be helpful. Thanks. Joanne ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Backup Database
On Wed, Oct 31, 2007 at 10:14:33AM -0700, Joanne Salerno wrote: > I'd like to set up one as a near real-time backup to the operational > database. Is it possible to incorporate a trigger upon update (or > insert) on the operational 8.24 database to initiate an update (or > insert) on the backup 7.4 database? You can do this with Slony, assuming nothing else needs to be written (on the replicated tables) into the 7.4 system. A -- Andrew Sullivan | [EMAIL PROTECTED] Never get involved in litigation. Your hair will fall out, your bones will turn to sand. And it will still be going on. --Tom Waits ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
