Re: [GENERAL] like optimization
Thank you all. Both the double index pg_trgm would be good solutions. On Oct 14, 2013, at 3:40 PM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch torsten.foert...@gmx.net wrote: On 12/10/13 20:08, Scott Ribe wrote: select * from test where tz = start and tz end and colb like '%foobar%' I think you can use an index only for wildcard expressions that are anchored at the beginning. So, select * from test where tz = start and tz end and colb like 'foobar%' can use an index on colb. You could perhaps select * from test where tz = start and tz end and colb like 'foobar%' union all select * from test where tz = start and tz end and reverse(colb) like 'raboof%' Then you need 2 indexes, one on colb the other on reverse(colb). You can have duplicates in the result set if the table contains rows where colb='foobar'. If that's a problem, use union distinct. Alternatively, if foobar is kind of a word (with boundaries), you could consider full-text search. pg_trgm module optimizes 'like with wildcards' without those restrictions. It's very fast for what it does. Because of the GIST/GIN dependency index only scans are not going to be used through pg_tgrm though. merlin -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cast user defined type to composite type
Hello!
I need use user defined type and operate it with low-level functions on
language C. In databasepreferred using composite type. Can i write more
performance casting that vector3_cast_vector3c?
// in C
typedef struct {
x, y, z double;
} vector3;
input, output and other functions...
// in database
CREATE TYPE vector3
(
internallength = 24,
input = vector3_in,
output = vector3_out,
...
);
CREATE TYPE vector3c AS
(
x double precision,
y double precision,
z double precision
);
CREATE OR REPLACE FUNCTION vector3_cast_vector3c ( v0 vector3 )
RETURNS vector3c AS
$BODY$
DECLARE
s text[];
v vector3c;
BEGIN
-- for example v0::text = '(0.0,1.0,0.0)'
s := string_to_array ( trim ( BOTH '()' FROM v0::text ), ',' );
v.x := s[1];
v.y := s[2];
v.z := s[3];
/*
or
v.x := vector3_x ( v0 ); -- call function on C code
v.y := vector3_y ( v0 );-- call function on C code
v.z := vector3_z ( v0 ); -- call function on C code
*/
RETURN v;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;
CREATE CAST ( vector3 AS vector3c ) WITH FUNCTION vector3_cast_vector3c
( v0 vector3 ) AS IMPLICIT;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Preserving the source code of views
Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets. I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments. Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement? --Brian
Re: [GENERAL] Preserving the source code of views
On 20/10/13 16:38, Brian Crowell wrote: Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets. I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments. Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement? --Brian The 'Real Experts' may well have more practically elegant solutions, but a couple possibilities I can think of are as follows: 1. You have access to the source of PostgreSQL, so you could modify the source code to preserve the source code of the views. 2. This might be able to be done using the extension mechanism, but I have not looked into that myself. So yes, you can do what you want, but not necessarily as easily as you would like. Cheers, Gavin
Re: [GENERAL] Preserving the source code of views
Hello 2013/10/20 Brian Crowell br...@fluggo.com Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets. I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments. Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement? I don't known about any way, how to do it (without hacking postgresql source code). PostgreSQL saves a views in preprocessed form from performance reasons. There are a few recommendation how to solve this issue - I never had a problem with it, because I use a different workflow. a) never modify a database object structure in database with admin tools. Use a SQL scripts ever. * a admin tools has not good has not good editors * there are no possibility to join related code together * there are no good versioning * a portability of handly written SQL scripts is significantly better than SQL scripts generated by admin tools I ever write a SQL scripts saved in files - then I can to push on one place (one file) related different objects - triggers, views, tables, procedures - with comments on file start, and with comments before any object. b) if you don't like @a, use a COMMENTs postgres=# create view simply as select 10; CREATE VIEW postgres=# comment on view simply is 'very simple view'; COMMENT postgres=# \dv+ List of relations Schema │ Name │ Type │ Owner │ Size │ Description ┼┼──┼───┼─┼── public │ simply │ view │ pavel │ 0 bytes │ very simple view (1 row) a 9.2 and newer PostgreSQL formats a materialized view - so it lost your formatting, but result will be in good format too. postgres=# create view simply as select 10 from pg_class where true; CREATE VIEW Time: 97.584 ms postgres=# \d+ simply View public.simply Column │ Type │ Modifiers │ Storage │ Description ──┼─┼───┼─┼─ ?column? │ integer │ │ plain │ View definition: SELECT 10 FROM pg_class WHERE true; For my work is very significant @a point - I wrote and I am writing usually database centric stored procedures centric applications and @a works perfect. For me a SQL code is code as any other - I use a my favourite editor, I use a GIT for versioning, I can simple distributed application to my customers. Regards Pavel --Brian
