Re: [SQL] Removing simliar elements from a set
On 26 Sep 2003 at 16:55, Josh Berkus wrote: > Dan, > > > I'm trying to remove items from a set which are similar to items in > > another set. > > > > In short, we remove all items from MASTER which are under the directories > > specified in MATCHES. > > from your example, you are trying to remove all directories which do *not* > match. What do you want, exactly? Josh and I talked on IRC about this. This is the result set I want: /ports/Mk/bsd.python.mk I want things from MASTER which do not match things in MATCHES. Josh suggested this: SELECT * FROM master WHERE NOT EXISTS ( SELECT * FROM matches WHERE master.pathname LIKE (matches.pathname || '/%')); Cheers. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Temporary tables
hi, I am using postgresql 7.3.2. Is there any function to determine whether a table exists in the database.Or is there any function that returns the current temp schema.I am using a pl/pgsql function that create and drop a temporary table.The procedure run correctly for the first time for each database connection. If I run the same procedure second time in the same connection it produces the error "ERROR: pg_class_aclcheck: relation 219389 not foundWARNING: Error occurred while executing PL/pgSQL function testFunWARNING: line 20 at SQL statement " Here is the function -CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof intAS'DECLARE --Aliases for parameters vSBAcNo ALIAS FOR $1; --local variables vRow RECORD; BEGIN -- create a tempory table to hold the numbers CREATE TABLE tempTable ( testNo int ) ; for vRow IN select Entryno from EntryTable LOOP return next vRow.Entryno; insert into tempTable values( vRow.Entryno); end loop; drop table tempTable; return; END;' LANGUAGE 'plpgsql'; - If i commented the "insert into tempTable values( vRow.Entryno);" linethe function works correctly. The problem is the oid of tempTable is kept when the function is first executed. the next execution creates another table with different oid. So the insert fails. I want to check whether the temporary table exist. If exist do not create the temporary table in subsequent calls and do not dorp it. This will solve the problem. When i searched the pg_class i found the temp table name more than once. ie, a temporary table is created for each connection.I cannot distingush the temp tables. But the tables are in different schema.Is there a method to get the current temporary schema? How postgres distinguishthis temp tables?.Is there a way to distinguish temporary tables.The entries in pg_class table is same except the schema.When i used the current_schema() function it returns public. There is a lot of functions that uses temporary tables. I think that there is an option when creating temp tables in postgres 7.4 . But no way to use 7.4 now it is a working database. can i write a function to check the existance of the temporary table...please help... jinujose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [SQL] Temporary tables
On Saturday 27 September 2003 14:31, George A.J wrote: > hi, > > I am using postgresql 7.3.2. Is there any function to determine > whether a table exists in the database.Or is there any function > that returns the current temp schema. > I am using a pl/pgsql function that create and drop a temporary table. > The procedure run correctly for the first time for each database > connection. If I run the same procedure second time in the same connection > it produces the error > > "ERROR: pg_class_aclcheck: relation 219389 not found > WARNING: Error occurred while executing PL/pgSQL function testFun > WARNING: line 20 at SQL statement " This is because plpgsql is "compiled" and so the reference to tempTable gets fixed the first time it is called. In your case, the oid was 219389. Now, the second time you call the function, the temp table gets re-created, gets a new OID and the old reference is no longer valid your insert line. There are two solutions: 1. Use pltcl/plperl or some other interpreted language that doesn't compile in table references. 2. Build your insert statement using EXECUTE ''INSERT INTO tempTable ''... This second passes the query string into the parser, so it works just fine for your example. I think some of this is covered in the manuals, you can certainly find plenty on it in the archives. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Temporary tables
"George A.J" <[EMAIL PROTECTED]> writes: > When i searched the pg_class i found the temp table name more than once. > ie, a temporary table is created for each connection.I cannot distingush > the temp tables. But the tables are in different schema. > Is there a method to get the current temporary schema? Not directly, but you could try something like perform * from pg_class where relname = 'mytable' and pg_table_is_visible(oid); if not found then ... -- create the table The visibility test would not succeed for temp tables belonging to other backends. (If 'mytable' is also used as the name of a regular table then this isn't quite good enough, but I think just avoiding such a name collision is easier than extending the check to reject non-temp tables.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] pg_class.relpages
Does the figures stored in pg_class.relpages include the pages
consumed by the toast tables linked to a normal table?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_class.relpages
Bertrand Petit wrote: > > Does the figures stored in pg_class.relpages include the pages > consumed by the toast tables linked to a normal table? No. See the chapter on monitoring disk space for more information. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pg_class.relpages
Bertrand Petit <[EMAIL PROTECTED]> writes: > Does the figures stored in pg_class.relpages include the pages > consumed by the toast tables linked to a normal table? No. The toast tables have their own pg_class.relpages entries ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_class.relpages
On Sat, Sep 27, 2003 at 08:26:16PM -0400, Bruce Momjian wrote:
> Bertrand Petit wrote:
> >
> > Does the figures stored in pg_class.relpages include the pages
> > consumed by the toast tables linked to a normal table?
>
> No. See the chapter on monitoring disk space for more information.
Okay. I eventually wrote the following query that lists tables
and indices sizes. A view similar to this query might my useful for
the general public if defined in the information schema.
SELECT pg_namespace.nspname AS schema,
COALESCE((SELECT class3.relname
FROM pg_class AS class3, pg_index
WHERE class1.oid=pg_index.indexrelid AND
class3.oid=pg_index.indrelid),
class1.relname) AS table,
CASE
WHEN class1.relkind='r' THEN NULL
ELSE class1.relname
END AS index,
(SELECT COALESCE(class1.relpages+SUM(class2.relpages),
class1.relpages) * 8
FROM pg_class AS class2
WHERE class2.oid IN (class1.reltoastrelid,
class1.reltoastidxid)) AS size
FROM pg_class AS class1, pg_namespace
WHERE class1.relnamespace = pg_namespace.oid AND
pg_namespace.nspname NOT IN ('pg_catalog',
'information_schema',
'pg_toast') AND
class1.relkind IN ('r', 'i')
ORDER BY class1.relnamespace ASC, class1.relname ASC,
class1.relkind DESC
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Mystery function error
As I am converting from Sybase I wanted to create a function which would replicate the behaviour of the sybase "Locate" command. The goal is to have locate( stra, strb) = position(strb in stra) where "position" is the standard postgres function for the index position of string "A" in string "B" My attempt at a function to do this task returns the error message ERROR: parse error at or near '"' I can not see why and have attached the simple function. -- CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; beginreturn position(searchstr in srcstr);' LANGUAGE 'plpgsql' VOLATILE; - Thanks very much Richard
Re: [SQL] Mystery function error
Richard Sydney-Smith wrote: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql' VOLATILE; You are missing the "end" keyword in there. Also, I'd think this function is IMMUTABLE not VOLATILE. CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); end; ' LANGUAGE 'plpgsql' IMMUTABLE; This could also be done as: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' select position($2 in $1) ' LANGUAGE 'sql'; HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Mystery function error
Richard, >--- CREATE OR REPLACE FUNCTION public.locate(bpchar, > bpchar) > RETURNS int4 AS > ' > -- search for the position of $2 in $1 > > declare > srcstr alias for $1; > searchstr alias for $2; > > begin > return position(searchstr in srcstr); You're missing "END;". > ' > LANGUAGE 'plpgsql' VOLATILE; Also, the function is not VOLATILE. It's IMMUTABLE, and STRICT as well. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Mystery function error
Richard, > The goal is to have > > locate( stra, strb) = position(strb in stra) Also, this will run faster if you do it as a SQL function: CREATE FUNCTION locate ( text, text ) RETURNS INT AS ' SELECT POSITION($2, $1); ' LANGUAGE SQL IMMUTABLE STRICT; -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
