[GENERAL] Re: Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[postgres user]

Also can you explain if I built Postgres from source on one platform lets
say RHEL_6 and deployed its artifacts like its binaries, libs and share on
a CentOS and tried building extensions against Postgres on CentOS are there
any dangers of doing that?

On Wed, Feb 1, 2017 at 8:34 PM, postgres user 
wrote:

> Hi,
>
> I am wondering about this question for a while with no definite answer to
> it, can someone explain me in detail to clear me out on the following
> question :
>
> What can go wrong or is it acceptable if I build Postgres from source with
> let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and CCFLAGS and when
> I try to install an extension for Postgres such as PostGIS using a
> different version of GCC lets say GCC 4.x.z with varying CFLAGS, CCFLAGS
> and CPPFLAGS and if I follow the same process for other non-contrib
> extensions? I want to know the theory behind this and it would serve me a
> great help to understand the systems aspect of the process as well.
>
> Thanks
>

[GENERAL] Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[postgres user]

Hi,

I am wondering about this question for a while with no definite answer to
it, can someone explain me in detail to clear me out on the following
question :

What can go wrong or is it acceptable if I build Postgres from source with
let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and CCFLAGS and when
I try to install an extension for Postgres such as PostGIS using a
different version of GCC lets say GCC 4.x.z with varying CFLAGS, CCFLAGS
and CPPFLAGS and if I follow the same process for other non-contrib
extensions? I want to know the theory behind this and it would serve me a
great help to understand the systems aspect of the process as well.

Thanks

[GENERAL] Testing an extension exhaustively?

[postgres user]

Hi,

If I have the Postgresql server installed on my machine i.e I have all the
bins, libs and share directories of the Postgresql and I have the libs and
sql's installed for one of the contrib extensions lets say "chkpass", how
does one go about testing this extension exhaustively on the server? I ask
this because I would want to do this manually first and then go about
automating the testing of this extension. So rather than just execute
CREATE EXTENSION and DROP EXTENSION I want some solid evidence that the
extension is working fine under all circumstances and is not crashing the
server at any moment? Looking for some new strategies and ideas to come my
way through this.

Thanks.

[GENERAL] Making changes to PostgreSQL's configure logic so as to have contrib modules installed in a specific directory and make them use PGXS?

[postgres user]

Hi,

I want to configure my PostgreSQL installation in such a manner such that
the contrib modules of the PostgreSQL distribution are stored in a specific
directory and they should use the PGXS extensions management system
supported by Postgres, as they are currently packaged along with Postgres
and follow a specific directory structure they are installed along with
Postgres but I don't want that. I want all those contrib module extensions
to use a separate pg_config and hence want their makefiles to use PGXS. How
do I go about doing that.

Thanks

[GENERAL] Re: Building PL/Perl procedural language without --with-perl configure flag

[postgres user]

The already installed Postgres edition was built using the same
installation procedure as mentioned in the docs, but without the use of
--with-perl flag. the point I ask the question is because I want to install
PL/Perl as a separate extension as one does with PostGIS and not along with
Postgres install. Is there a way out to solve that problem of building the
PL/Perl language by somehow creating a custom Makefile as we have for
contrib extensions or PostGIs etc... and then giving it the path of
pg_config hence leading to an installation?

[GENERAL] Building PL/Perl procedural language without --with-perl configure flag

[postgres user]

Hi,

I am trying to build PL/Perl procedural language to my PostgreSQL server
installation but I want to do it without passing --with-perl flag to the
configure script of PostgreSQL. It would also help if someone can explain
the meaning of --with-perl flag in a detailed fashion altogether as it
might help me determine the actual process of installing PL/Perl. There are
two parts to the question however how do I get the source of PL/Perl
firstly and how do I build it against an already built version of
PostgreSQL as in when I have the (bin, lib, share) for PostgreSQL and what
all pre-requisites are needed to install PL/Perl in the first place?

Thanks

Re: [GENERAL] Indicating DEFAULT values in INSERT statement

[Postgres User]

Good idea but you're right, if the default value is an expression such as a
the next serial value, it will need to be executed first.  If Execute
doesn't return the interpreted value, I don't know of a way to make it
work..

On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina <
diegoaugustomol...@gmail.com> wrote:

> This is a bit hacky, but it may work (I haven't tried it yet). If it
> works let us know and if it doesn't then maybe we can debug it and get
> something useful, or move on to another solution.
>
> 
>  INSERT INTO public.test
> (
> userid, object_id, user_notes, object_status, created_ts
> )
> VALUES
> (
> p_userid, p_obj_id, p_user_notes, p_obj_status,
>Case When p_created_ts Is Not Null Then p_created_ts Else
> ( -- You may want to be sure the field has a default value.
>SELECT d.adsrc -- or should it be d.adbin?
>FROM
>pg_catalog.pg_attribute a INNER JOIN
>pg_catalog.pg_attrdef d ON (a.attnum =
> d.adnum)
>WHERE
>a.attname = 'created_ts' AND
>a.attrelid = 'public.test'::REGCLASS AND
>d.adrelid = 'public.test'::REGCLASS
>) End
> );
> 
>
> Well (thinking it thoroughly) it won't work at all as is. It will just
> put the source code of the default expression but you would need to
> *interpret* it first.
> Looked for a way to do this (without `EXECUTE') but couldn't find it.
> Sorry.
>
> --
> Diego Augusto Molina
> diegoaugustomol...@gmail.com
>
> ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
> desestimados.
> EN: Please, avoid attaching Microsoft Office documents. They shall be
> discarded.
> LINK: http://www.gnu.org/philosophy/no-word-attachments.html
>

Re: [GENERAL] Indicating DEFAULT values in INSERT statement

[Postgres User]

Thanks for the previous posts. I am in fact running 9.0 'nix and am unable
to find a way to embed DEFAULT within an expression.
I can always rewrite the function to call EXECUTE but that's not a very good
solution in this case because many of our db functions are code generated.

On Tue, Aug 9, 2011 at 4:47 PM, Diego Augusto Molina <
diegoaugustomol...@gmail.com> wrote:

>  Hi, when posting SQL it may be a good practice to post your PG
> version so that answers may be more accurate and better fit your
> needs. In this case, I don't think you'll be able to do what you are
> trying to, because as of my understanding the "DEFAULT" is not part of
> an expression but a keyword itself which is to be written *instead* of
> an expression. I get your point and seems pretty fair to me, but if
> I'm right you don't have access to the default value of the column
> from within an expression.
>  Assuming you're using the latest stable version of PostgreSQL (9.0
> as of today), you can check the following page for an online reading
> reference of the INSERT statement:
> http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
>  If you look carefully, you'll see that for the value of a column you
> may write: the "DEFAULT" keyword [exclusive or] an expression (which
> includes specific values).
>  The  [exclusive or] is deduced because of the pipe (`|') between the
> two choices which are embraced by curly braces.
>  So, if you were able to use the "DEFAULT" keyword inside an
> expression, instead of having the "{ expression | DEFAULT }" syntax
> you would have the "expression" syntax, which would include the case
> of having just "DEFAULT" as an expression. It's just a matter of
> understanding the syntax.
>
>  Cheers!
>
> --
> Diego Augusto Molina
> diegoaugustomol...@gmail.com
>
> ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
> desestimados.
> EN: Please, avoid attaching Microsoft Office documents. They shall be
> discarded.
> LINK: http://www.gnu.org/philosophy/no-word-attachments.html
>

[GENERAL] Indicating DEFAULT values in INSERT statement

[Postgres User]

Hi,

According to the docs, the DEFAULT keyword lets you explicitly insert a
field's default value in an INSERT statement.

>From a db function, I'd like to force the use of default when an input
parameter is null.  This syntax looks correct but fails to compile.

Any suggestions?

INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
Case When p_created_ts Is Not Null Then p_created_ts Else DEFAULT
End
);

I tried replacing the Case statement as follows, but it fails also:
 Coalesce(p_created_ts, DEFAULT)

Thanks

Re: [GENERAL] Selecting from table into an array var

[Postgres User]

Great call, someone did hose the data.  Oddly enough the circular
reference caused no problem when running the stand alone recursive SQL
(with clause).

On Sat, Dec 19, 2009 at 10:41 AM, Merlin Moncure  wrote:
> On Sat, Dec 19, 2009 at 1:30 PM, Postgres User
>  wrote:
>>
>> Thanks, your syntax does compile and run.
>>
>> This is where it gets interesting.  With your syntax (and variations
>> of it), I'm able to successfully compile and execute.  However, as
>> soon as I add a dozen rows to the table, the query failes to complete.
>>  It runs until timing out.
>> This prolem does not occur when I remove the SELECT INTO ARRAY
>> statement and simply run the recursive query.
>>
>> Has anyone else seen this behavior?
>
> are you sure you don't have a circular dependency?  maybe you have a
> variable/column name clash?
>
> merlin
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to call a function that returns a refcursor ?

[Postgres User]

Hi,

I have a function that returns a refcursor that I need to call from a
second function.  In the second function, I'd like to read a column
value from each row.  However, I'm having a problem accessing the rows
of the refcursor.
Can anyone point me to a working example of how to pull this off?

This is the latest iteration of the function code that I've tried to
run without any success:

CREATE OR REPLACE FUNCTION "return_cursor" (
)
RETURNS SETOF "pg_catalog"."refcursor" AS
$body$
DECLARE
  rf refcursor;
BEGIN
  OPEN rf FOR
SELECT * FROM category;
  RETURN Next rf;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION "test"."read_cursor" (
)
RETURNS integer [] AS
$body$
DECLARE
  r record;
  cat_list integer[];
BEGIN
  FOR r IN SELECT * FROM test.return_cursor() LOOP
cat_list = cat_list || r.category_id;
  END LOOP;
  Return cat_list;
END;
$body$
LANGUAGE 'plpgsql';

with this table struct:

CREATE TABLE "category" (
 "category_id" SERIAL,
 "parent_id" INTEGER,
 "category_name" VARCHAR(50)
) WITHOUT OIDS;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Selecting from table into an array var

[Postgres User]

On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure  wrote:
> On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
>  wrote:
>>
>> BEGIN
>> SELECT array_agg(category_id) INTO cat_list FROM (
>>         WITH RECURSIVE subcategory AS
>>                (
>>                SELECT * FROM category
>>                WHERE category_id = p_category_id
>>
>>                UNION ALL
>>
>>                        SELECT c.*
>>                        FROM category AS c
>>                        INNER JOIN subcategory AS sc ON (c.category_id = 
>> sc.parent_id)
>>                )
>>                SELECT category_id FROM subcategory
>>                ORDER BY Coalesce(parent_id, 0) DESC
>>  ) c;
>> END;
>
> works for me (i didn't put any data in though).  the above is probably
> better written using array() notation as I mentioned above:
>
> SELECT array
> (
>  WITH RECURSIVE subcategory AS
>  (
>    SELECT * FROM category
>      WHERE category_id = p_category_id
>    UNION ALL
>      SELECT c.*
>        FROM category AS c
>        INNER JOIN subcategory AS sc ON (c.category_id = sc.parent_id)
>  )
>  SELECT category_id FROM subcategory
>  ORDER BY Coalesce(parent_id, 0) DESC
> ) INTO cat_list;
>
> Also if you want more than just the ID stacked in the array the above
> can be reworked in to an array of the 'category' type.
>
> merlin

Thanks, your syntax does compile and run.

This is where it gets interesting.  With your syntax (and variations
of it), I'm able to successfully compile and execute.  However, as
soon as I add a dozen rows to the table, the query failes to complete.
 It runs until timing out.
This prolem does not occur when I remove the SELECT INTO ARRAY
statement and simply run the recursive query.

Has anyone else seen this behavior?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Selecting from table into an array var

[Postgres User]

On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure  wrote:
> On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule  
> wrote:
>> 2009/12/19 Postgres User :
>>> Hi,
>>>
>>> I'm trying to write a very simple function statement to select a
>>> single integer field from a table and save it into an int array. For
>>> some reason I can't seem to find the correct syntax:
>>>
>>> CREATE TABLE sample (
>>>    id  integer
>>> );
>>>
>>> and then within a function:
>>>
>>> my_array int[];
>>> my_array = SELECT ARRAY(id) FROM sample;
>>>
>>>
>>> This syntax and variations of it don't work.  Can anyone show me the
>>> correct approach?
>>>
>>
>> Hello
>>
>> please try SELECT ARRAY(SELECT id FROM sample) or SELECT array_agg(id)
>> FROM sample - if you have 8.4
>
> yup:
>
> array() vs array_agg() vs array[]...which to use?
>
> *) use array[] when building list of scalar values
> *) use array_agg when aggregating (you need to group by something)
> *) use array() everywhere else
>
> merlin
>

Thanks for the replies.  I had already tried array_agg (on 8.4) and
ultimately found that the errors were caused by a recursive query.
When I replace the recursive query with a basic SELECT statement, the
code below works.
Apparently, you cannot combine an aggregate function such as
arrayagg() with a recursive SQL statement.  This may be a PG bug.

For example, this fails:

DECLARE
cat_list integer[];

BEGIN
SELECT array_agg(category_id) INTO cat_list FROM (
 WITH RECURSIVE subcategory AS
(
SELECT * FROM category
WHERE category_id = p_category_id

UNION ALL

SELECT c.*
FROM category AS c
INNER JOIN subcategory AS sc ON (c.category_id = 
sc.parent_id)
)
SELECT category_id FROM subcategory
ORDER BY Coalesce(parent_id, 0) DESC
  ) c;
END;

with this table def

CREATE TABLE "category" (
  "category_id" SERIAL,
  "parent_id" INTEGER,
  "category_name" VARCHAR(50)
) WITHOUT OIDS;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Selecting from table into an array var

[Postgres User]

Hi,

I'm trying to write a very simple function statement to select a
single integer field from a table and save it into an int array. For
some reason I can't seem to find the correct syntax:

CREATE TABLE sample (
id  integer
);

and then within a function:

my_array int[];
my_array = SELECT ARRAY(id) FROM sample;


This syntax and variations of it don't work.  Can anyone show me the
correct approach?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to check status of bug (42804: structure of query does not match error where using RETURN QUERY) ?

[Postgres User]

Hi,

I've encountered a serious Postgres bug which apparently has a fix
that hasn't been released in a patch.  How or where can I look up its
status?

This is a serious bug- it basically renders RETURN QUERY useless in
any environment where you don't have the option of dropping and
re-creating a table, so I'm surprised to see that it's been
outstanding for this long.

42804: structure of query does not match error where using RETURN QUERY

Tom Lane wrote:
> Michal Szymanski  writes:
> >> Exactly what modifications did you make?
>
> > We have added few new columns and we delete one column.
>
> [ experiments... ]  Hmm, looks like RETURN QUERY isn't too bright about
> dropped columns in the function result type.  I'm not too sure about how
> hard this is to fix, but in the meantime you will need to dump and
> recreate/reload that particular table in order to get it to work.

Is this a TODO?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-bugs mailing list (pgsql-b...@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: Re: 42804: structure of query does not match error where using
RETURN QUERY
by Tom Lane-2 Jun 02, 2009; 07:47am :: Rate this Message:- Use
ratings to moderate (?)
Reply | Reply to Author | Print | View Threaded | Show Only this Message
Bruce Momjian  writes:
> Tom Lane wrote:
>> [ experiments... ]  Hmm, looks like RETURN QUERY isn't too bright about
>> dropped columns in the function result type.  I'm not too sure about how
>> hard this is to fix, but in the meantime you will need to dump and
>> recreate/reload that particular table in order to get it to work.

> Is this a TODO?

It's already there.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres database performance on 6 core Opteron vs 4 core Xeon

[Postgres User]

Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase?  Or has anyone compared these 2
processor options before ordering a Postgres server?

The 6 core processor should offer a clear edge but these AMD
processors are cited for relatively slow L3 cache speeds compared to
their Intel counterparts.

4-Core Intel Xeon E5520 (2.26 GHz) Processor      VS
6-Core AMD Opteron Model 2425 HE (2.1GHz} Processor

Any comments?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres database performance on 6 core Opteron vs 4 core Xeon

[Postgres User]

Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase?  Or has anyone compared these 2
processor options before ordering a Postgres server?

The 6 core processor should offer a clear edge but these AMD
processors are cited for relatively slow L3 cache speeds compared to
their Intel counterparts.

4-Core Intel Xeon E5520 (2.26 GHz) Processor  VS
6-Core AMD Opteron Model 2425 HE (2.1GHz} Processor

Any comments?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] interface for "non-SQL people"

[Postgres User]

EMS SQL Manager has a visual query builder, but it's a commerical
product, ie it aint free.

On Tue, Oct 6, 2009 at 12:47 PM, pere roca  wrote:
>
>
>  hi,
>  some nice tool over there to let non-SQL knowing people to construct their
> queries? I'm using pgAdmin III but I know some SQL.
>  there is no other option than constructing an HTML with forms, drop-down
> menus...?
>
>  thanks,
>  pERE
> --
> View this message in context: 
> http://www.nabble.com/interface-for-%22non-SQL-people%22-tp25775414p25775414.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Composite types and tables - when to use?

[Postgres User]

Browsing the docs last night, I realized that I've never taken
advantage of Postgres' powerful composite types.  But a question came
to mind- in what scenarios should you use a composite type in a table
structure?  That is, I can see the benefits of a composiite type
insofar as it essentially lets you add virtual  tuples to a table
without having to alter the table structure to add new fields.
Instead you can simply extend the composite type.  But why take this
approach?

http://www.postgresql.org/docs/8.4/static/rowtypes.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Composite types and tables - when to use?

[Postgres User]

Browsing the docs last night, I realized that I've never taken
advantage of Postgres' powerful composite types.  But a question came
to mind- in what scenarios should you use a composite type in a table
structure?  That is, I can see the benefits of a composiite type
insofar as it essentially lets you add virtual  tuples to a table
without having to alter the table structure to add new fields.
Instead you can simply extend the composite type.  But why take this
approach?

http://www.postgresql.org/docs/8.4/static/rowtypes.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Insert - Default in a condition expression ??

[Postgres User]

>> >
>> > I'm trying to write an INSERT INTO statement that will use a DEFAULT
>> > value when an input parameter is null.
>> >
>> Neither of my 2 methods are pretty.
>> 1) Use a trigger.
>> 2) Grab and cast the default value from the information_schema.columns
>> view and plug it in.
>>
>> Another option is to build your insert sql as a string and then execute it.
>
> Another option:
>
> rewrite your function:
>
> CREATE OR REPLACE FUNCTION "name_add" (p_name varchar, p_created_date
> date) RETURNS integer AS $$ BEGIN case when $2 is null then INSERT INTO
> names(name, created_date) values (p_name,  default); else insert into
> names values ($1, $2); end case; return 1; end; $$ language plpgsql;
>
> Andreas Kretschmer

Again, this approach works for a simple example.  But for a larger
function with any number of input params and multiple columns with
default values, it's not practical for an ORM code generator.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Insert - Default in a condition expression ??

[Postgres User]

>> I'm trying to write an INSERT INTO statement that will use a DEFAULT
>> value when an input parameter is null.
>>
> Neither of my 2 methods are pretty.
> 1) Use a trigger.
> 2) Grab and cast the default value from the information_schema.columns
> view and plug it in.
>
> Another option is to build your insert sql as a string and then execute it.
>
> Sim
>

Thanks... those approaches came to mind.  But I need something more
flexible as the Insert functions are generated by an ORM layer.  It
has to be simpler.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using Insert - Default in a condition expression ??

[Postgres User]

Hi,

I'm trying to write an INSERT INTO statement that will use a DEFAULT
value when an input parameter is null.

Here's the function that fails to compile.  I tried replacing Coalesce
with a Case statement but that fails as well.  Note that if you
replace the condition with a simple 'Default' it compiles
successfully.  Any ideas?


CREATE OR REPLACE FUNCTION "name_add" (
p_name varchar,
p_created_date date
) RETURNS integer AS
$body$
DECLARE
BEGIN
INSERT INTO names
(
name,
created_date
)
VALUES
(
p_name,
Coalesce(p_created_date, DEFAULT)
);
Return 1;
END ;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Alternative to temp tables?

[Postgres User]

Hi,

I have a simple function that returns a set of rows:

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF record AS
$$
BEGIN
RETURN QUERY SELECT * FROM people WHERE last_name = 'jones';
END
$$
LANGUAGE 'plpgsql'

In a separate function, I call the function and store the results in a
temp table using this syntax:

INSERT INTO tmp_tbl SELECT * FROM foo()


This works, but I'd like to know if there's another way to hold the
results.  Can I get the results from foo() and store those in a local
var such as

recs record[]   OR
recs people[]

Or are temp tables the only way to hold table-based results?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Schema search_path and $user

[Postgres User]

Thanks for the link, I wasn't reading the right page(s) in the documentation.

On Mon, Jul 6, 2009 at 12:19 PM, Tom Lane wrote:
> Postgres User  writes:
>> In the docs, I see repeated references to $user in the postgresql.conf
>> schema search_path setting such as:
>
>> search_path = '"$user",public'
>
>> But I don't see any info on the meaning of '$user' here.
>
> I guess you didn't read the actual documentation of search_path:
> http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-SEARCH-PATH
> It says
>
> The value for search_path has to be a comma-separated list of schema
> names. If one of the list items is the special value $user, then the
> schema having the name returned by SESSION_USER is substituted, if there
> is such a schema. (If not, $user is ignored.)
>
>                        regards, tom lane
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Schema search_path and $user

[Postgres User]

In the docs, I see repeated references to $user in the postgresql.conf
schema search_path setting such as:

search_path = '"$user",public'  

But I don't see any info on the meaning of '$user' here.  Is $user
some kind of variable within postgresql.conf that refers to the
current user?  Can it be replaced with a real group name to control
schema search_paths for specific groups/users?

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Maintaining user roles and permissions in Postgres - general question

[Postgres User]

Does anyone have a recommendation for maintaining user permissions on
a changing database?  The lack of an option to grant specific rights
to all objects of a given type within a Postgres db obviously places
the burden on the administrator to keep roles updated as objects are
added and dropped from a given database.

Unfortunately for us, we don't have a dedicated db admin, so this task
falls into the hands of developers who are probably less adapt at this
kind of task ;)

Is there a utility or set of scripts out there that helps a db owner
with permissions admin?

Thanks

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to get the size of non fixed-length field from system catalog ?

[Postgres User]

Hi,

I'm writing a small ORM tool and have written a number of queries to
retrieve table metadata.  One piece of data that I'm having trouble
hunting down is the size of a CHAR field.  For example, one table has
a 'user_id' column of type CHAR(36).  But when I look at the
pg_attribute and pg_type tables, I can't seem to find this size value
of 36.

Can anyone share the SQL that returns the size of a CHAR?  It is NOT
the 'typlen' column.  The answer may be the 'typelem' column, but I
can't find details on how to decode it.

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SQL to return all function parameters- its working

[Postgres User]

Based on replies to another post (recommending use of
'generate_series'), I was able to write the following query that
returns all paramters of a given function.
Only one interesting thing to note- in order to return the proper
argument type, I had to use
  proargtypes[i - 1] when I expected this to work:proargtypes[i]

Any feedback would be appreciated...


SELECT n.nspname AS name_space,
p.proname AS function_name,
p.oid AS function_oid, t.typname AS rettype,
p.prosrc AS body,
   argument, argument_type
FROM pg_proc p
INNER JOIN (SELECT oid, proargnames[i] AS argument, proargtypes[i-1]
AS argument_type
 FROM
(SELECT oid, proargnames, proargtypes,
generate_series(1, array_upper(proargnames,1)) AS i
FROM pg_proc) s
 ) arg
ON p.oid = arg.oid
INNER JOIN pg_namespace n
ON p.pronamespace = n.oid
LEFT OUTER JOIN pg_type t
ON t.oid = p.prorettype
-- WHERE p.proname =  'func_name'

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Converting each item in array to a query result row

[Postgres User]

Thanks for all the replies.  I'm going to post the results of using
the recommended approach in another thread.

On Fri, May 29, 2009 at 1:18 PM, Adam Ruth  wrote:
> Good point, I should have specified 8.3.7.
>
> Just one more reason to anxiously anticipate upgrading to 8.4.
>
>
>
> On 30/05/2009, at 2:56 AM, Tom Lane wrote:
>
>> Adam Ruth  writes:
>>>
>>> Always test your performance assumptions. The plpgsql function is
>>> faster than the sql function, a lot faster on smaller arrays.
>>
>> And, of course, it also pays to be precise about what you're testing
>> and on what.  Set-returning SQL functions got a lot faster in 8.4.
>> Using CVS HEAD on a not-very-fast machine, I get these timings for
>> the attached script (1 loop iterations in all cases)
>>
>>                        10 elements     100 elements    1000 elements
>>
>> built-in unnest         2.44            6.52            47.96
>> SQL function            2.52            6.50            46.71
>> plpgsql function        3.63            12.47           101.68
>>
>> So at least in this specific test condition, there's not much
>> perceptible difference between the SQL function and the builtin,
>> while plpgsql lags behind.
>>
>>                        regards, tom lane
>>
>>
>> create or replace function testit(n int, l int) returns float8 as $$
>> declare arr int[];
>>  st timestamptz;
>>  et timestamptz;
>> begin
>>  arr := '{}';
>>  for i in 1 .. n loop
>>   arr[i] = i;
>>  end loop;
>>  st := clock_timestamp();
>>  for i in 1 .. l loop
>>   perform count(*) from unnest(arr); -- or unnest_sql or unnest_plpgsql
>>  end loop;
>>  et := clock_timestamp();
>>  return extract(epoch from et - st);
>> end $$ language plpgsql;
>>
>> CREATE or replace FUNCTION unnest_sql(anyarray) RETURNS SETOF anyelement
>> AS
>> $_$
>> SELECT ($1)[i] FROM generate_series(array_lower($1,1),array_upper($1,1))
>> i;
>> $_$
>>   LANGUAGE sql IMMUTABLE;
>>
>> create or replace function unnest_plpgsql(_a anyarray) returns setof
>> anyelement as $$
>> begin
>>        for i in array_lower(_a,1) .. array_upper(_a,1) loop
>>                return next _a[i];
>>        end loop;
>>        return;
>> end;
>> $$ language plpgsql strict immutable;
>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Converting each item in array to a query result row

[Postgres User]

Hi,

I'd writing a query against a function (pg_proc) that contains 2
fields of an array type.  Ideally, I'd like to select 1 row from the
table, but return a query row for each item in the array.

For example, if one row contains the array {"a", "b", "c"}
I'd like the query to return 3 rows, one for each of these elements.

Any idea if this is possible?

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to restore a SQL-ASCII encoded database to a new UTF-8 db?

[Postgres User]

Hi,

I have a database that was created with SQL-ASCII encoding
(unfortunately).  I ran pg_restore to load the struct and data into a
new database with UTF-8 encoding but no surprise- I'm seeing this
error for a number of tables:

pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence for encod
ing "UTF8"

Any idea on how I can copy the data between these databases without
any data loss?  For some reason I thought that a conversion to Unicode
would be easy.

Thanks

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Max array size

[Postgres User]

Hi,

Does anyone know the maximum number of elements allowed in a
one-dimensional array?  It looks like my script may add at least a
couple million, not sure if it will hit a limit...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Scripting function definitions as SQL?

[Postgres User]

Yes, but I'm doing this from a Delphi program in Windows and that's
why I'm looking for a solution that's SQL-based.

It would be nice if one of the system catalog views handled it.

> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.

On Sun, May 11, 2008 at 11:43 AM, hubert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Sun, May 11, 2008 at 11:28:37AM -0700, Postgres User wrote:
> > Unfortunately I didn't see a way to tell pg_dump to dump only objects
> > of a specific type, like functions or sequences.  It requires
> > additional coding to parse the output and that's less than ideal...
>
> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.
>
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA.  here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Scripting function definitions as SQL?

[Postgres User]

Unfortunately I didn't see a way to tell pg_dump to dump only objects
of a specific type, like functions or sequences.  It requires
additional coding to parse the output and that's less than ideal...

>  Does pg_dump not do what you want?

On Sun, May 11, 2008 at 6:49 AM, Martijn van Oosterhout
<[EMAIL PROTECTED]> wrote:
> On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
> > Has anyone written a function that scripts out all the functions in a
> > database as full SQL statements (Create Function.)
> >
> > I found the below SQL will return all the fields needed to build a SQL
> > statement, but it would take some work to combine the field values
> > correctly to get the right format.  So does anyone know if the code
> > has already been written by someone else?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Scripting function definitions as SQL?

[Postgres User]

Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.)

I found the below SQL will return all the fields needed to build a SQL
statement, but it would take some work to combine the field values
correctly to get the right format.  So does anyone know if the code
has already been written by someone else?


SELECT p.proname AS name, p.oid, p.proargtypes AS args, ds.description
, p.prorettype AS rettype,
 p.proretset, p.probin, p.proisstrict AS strict, p.prosrc AS body,
l.lanname AS lang,
 u.usename, p.prosecdef, p.provolatile, p.proisagg, n.nspname,
proargnames, p.proargmodes, p.proallargtypes
FROM pg_proc p
LEFT OUTER JOIN pg_description ds ON ds.objoid = p.oid
INNER JOIN pg_namespace n ON p.pronamespace = n.oid
INNER JOIN pg_language l ON l.oid = p.prolang
LEFT OUTER JOIN pg_user u ON u.usesysid = p.proowner
WHERE n.nspname = 'main'
ORDER BY p.proname, n.nspname

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trigger transactions

[Postgres User]

Question answered- needed to move Insert statement before Update in
main function.

On Fri, Mar 21, 2008 at 6:42 PM, Postgres User
<[EMAIL PROTECTED]> wrote:
> if a function includes this SQL:
>
>  Update Table1  Set field_1 = 'ab';
>  Insert Table2(field_2) VALUES('cd');
>
>
> and I create an update trigger on Table1:
>
>  Create Trigger Table1_Update AFTER Update
>  On Table1 FOR EACH ROW:
>
>  Select * From Table2
>
>
> will the Select statement in the trigger see the row that I inserted
> in the main function?
> if not, is there another way to write these statements to that it does?
>
> thanks
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trigger transactions

[Postgres User]

if a function includes this SQL:

  Update Table1  Set field_1 = 'ab';
  Insert Table2(field_2) VALUES('cd');


and I create an update trigger on Table1:

  Create Trigger Table1_Update AFTER Update
  On Table1 FOR EACH ROW:

  Select * From Table2


will the Select statement in the trigger see the row that I inserted
in the main function?
if not, is there another way to write these statements to that it does?

thanks

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Regex query not using index

[Postgres User]

Tom,

I was looking for another approach but didn't come across that array
syntax in my searches (perhaps because it's newer.  Thanks for a
solution.

Now to end my fixation, one last item.  What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres will ignore when processing the query?

This syntax results in a seq scan:   WHERE fielda = Coalesce(param, fielda)
because it applies only to non-nulls

Is there another way to write this- perhaps using your array syntax on
an empty array?  Basically I'd PG to ignore the condition just as it
ignores   WHERE 1 = 1


On Wed, Feb 20, 2008 at 8:31 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Postgres User" <[EMAIL PROTECTED]> writes:
>
> > My users are developers and the goal was to accept a simple
>  > comma-delimited list of string values as a function's input parameter.
>  >  The function would then parse this input param into a valid regex
>  > expression.
>
>  Why are you fixated on this being a regex?  If you aren't actually
>  trying to expose regex capabilities to the users, you'll just be having
>  to suppress a bunch of strange behaviors for special characters.
>
>  ISTM that the best solution is to use an array-of-text parameter,
>  along the lines of
>
> where name = any (array['Smith', 'Jones', ...])
>
>  For what you're doing, you'd not actually want the array[] syntax,
>  it would look more like
>
> where name = any ('{Smith,Jones}'::text[])
>
>  This should optimize into an indexscan in 8.2 or later.
>
> regards, tom lane
>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Regex query not using index

[Postgres User]

Tino,

My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
 The function would then parse this input param into a valid regex
expression.

I was trying to write a function that lets me avoid using Execute
 and instead write in-line SQL with all the benefits of
pre-compilation and optimization.

Regex offers such a technique- IF it could understand regex that
represented a set of logical ORs and do an index scan (my rule is to
avoid seq-scans)

An example of regex that allows you to use in-line SQL with a
condition equivalent to many OR conditions when using basic comparison
operators:

select * from table1
where name ~ '.*' '^Smith$' |^Jones$':

And this works very well- except for the seq scan instead of an index scan



On Feb 20, 2008 2:31 AM, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> Postgres User wrote:
> > im trying to allow the client to pass a varchar param into my
> > function, and want to avoid any parsing of the parameter inside the
> > function, or code to build a sql string.
> >
> > if the function can use this code, it will be compiled and optimized
> > (unlike a dynamic sql stirng)
> >
> > select * from mytable where fielda ~ p_param
>
> No, you should never let users specify raw regex. at best they can
> hog down your server. Regex is a state engine and you can create
> endless loops.
>
> Maybe we can see the overall picture of your query?
>
> Regards
> Tino
>

---(end of broadcast)---
TIP 1: 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: [GENERAL] Regex query not using index

[Postgres User]

a final question: why does this syntax do a seq scan + filter:

select * from tablea where fielda = fielda   -or-  select * from
tablea where fielda in (fielda)

while this syntax results in no filter, seq scan only

select * from tablea where 1 = 1

it seems that both where clauses should be ignored by the optimizer-
or am i missing something

On Feb 19, 2008 9:45 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Postgres User" <[EMAIL PROTECTED]> writes:
> > Yes that works, but the whole point of the exercise is replace many OR
> > statements with 1 regex expression.  So it's not what I'm looking for.
>
> Unfortunately, Postgres is not as intelligent as you are.  There is
> no mechanism to rewrite a multi-branch regex condition into multiple
> indexscans.  I recommend going back to the OR's.
>
> regards, tom lane
>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Regex query not using index

[Postgres User]

by the way, your example works fine unless it's a null value or empty string
unfortunately, postgres isn't smart enough to know that the when
p_param below is null, that the WHERE condition can be ignored

select * from table where name in (Coalesce(p_param, name))

which is the same as:   select * from table where name in (name)

postgres does a row scan on the above sql.  too slow.


On Feb 19, 2008 9:34 PM, Chris <[EMAIL PROTECTED]> wrote:
> Postgres User wrote:
> > Yes that works, but the whole point of the exercise is replace many OR
> > statements with 1 regex expression.  So it's not what I'm looking for.
>
> Why do you want it done this way?
>
> You can build an array of strings to check and use an in clause.
>
> Using php :
>
> $checks = array('AA', 'BA');
>
> $query = "select * from table where name in ('" . implode("','",
> $checks) . "')";
>
> and it should use an index (up to a point anyway).
>
> --
>
> Postgresql & php tutorials
> http://www.designmagick.com/
>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Regex query not using index

[Postgres User]

doh!  tom, let me know if you decide to hack out a fix for this one of
these nights ;)
thanks for your help.

On Feb 19, 2008 9:45 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Postgres User" <[EMAIL PROTECTED]> writes:
> > Yes that works, but the whole point of the exercise is replace many OR
> > statements with 1 regex expression.  So it's not what I'm looking for.
>
> Unfortunately, Postgres is not as intelligent as you are.  There is
> no mechanism to rewrite a multi-branch regex condition into multiple
> indexscans.  I recommend going back to the OR's.
>
> regards, tom lane
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Regex query not using index

[Postgres User]

im trying to allow the client to pass a varchar param into my
function, and want to avoid any parsing of the parameter inside the
function, or code to build a sql string.

if the function can use this code, it will be compiled and optimized
(unlike a dynamic sql stirng)

select * from mytable where fielda ~ p_param

(where p_param is the input parameter)

On Feb 19, 2008 9:34 PM, Chris <[EMAIL PROTECTED]> wrote:
> Postgres User wrote:
> > Yes that works, but the whole point of the exercise is replace many OR
> > statements with 1 regex expression.  So it's not what I'm looking for.
>
> Why do you want it done this way?
>
> You can build an array of strings to check and use an in clause.
>
> Using php :
>
> $checks = array('AA', 'BA');
>
> $query = "select * from table where name in ('" . implode("','",
> $checks) . "')";
>
> and it should use an index (up to a point anyway).
>
> --
>
> Postgresql & php tutorials
> http://www.designmagick.com/
>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Regex query not using index

[Postgres User]

Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression.  So it's not what I'm looking for.

On Feb 19, 2008 9:16 PM, Chris <[EMAIL PROTECTED]> wrote:
> Postgres User wrote:
> > Thanks, my dumb mistake.
> > I need to perform the equivalent of a WHERE clause OR expression using
> > regex to match exact strings.
> >
> > _
> >
> > this example hits the index:
> > select * from eod where name ~ '^BA$'
> >
> > but when I try to add another possible value to the regex, it does a row 
> > scan:
> > select * from eod where name ~ ^BA$|^AA$'
> >
> > both of these statements return the right results, but the 2nd ignores
> > the index even though both values are left-anchored.
> >
> > any workaround- this behavior doesn't seem to make sense
>
> try changing it to
>
> select * from eod where (name ~ '^BA$' or name ~ '^AA$')
>
> though in this example they should both be name = 'XX' rather than regex'es.
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Regex query not using index

[Postgres User]

Thanks, my dumb mistake.
I need to perform the equivalent of a WHERE clause OR expression using
regex to match exact strings.

_

this example hits the index:
select * from eod where name ~ '^BA$'

but when I try to add another possible value to the regex, it does a row scan:
select * from eod where name ~ ^BA$|^AA$'

both of these statements return the right results, but the 2nd ignores
the index even though both values are left-anchored.

any workaround- this behavior doesn't seem to make sense

On Feb 19, 2008 8:45 PM, Erik Jones <[EMAIL PROTECTED]> wrote:
>
> On Feb 19, 2008, at 9:32 PM, Postgres User wrote:
>
> > I'm running a simple query on 8.2.  With this syntax, Explain indicate
> > that the index is scanned:
> > select * from eod where name = 'AA'
> >
> > However, when I change the query to use simple regex:
> > select * from eod where name ~ 'AA'
> >
> > now Explain indicates a seq scan:
> > Index Scan using equity_eod_symbol_idx on equity_eod  (cost=0.00..8.27
> > rows=1 width=149)
> > Index Cond: ((symbol)::text = 'AA'::text)
> >
> > Is there any way to 'encourage' Postgres to hit the index when using
> > regex?  Do I need to create a functional index or something?
> > Without the index in play, I really can't use regex on any of my
> > larger tables.
>
> You need it to be anchored:
>
> select * from eod where name ~ '^AA';
>
> If you're looking to be able to use indexes for searches within a
> string then, for 8.2, you'll need to check out tsearch2.
>
> Erik Jones
>
> DBA | Emma(R)
> [EMAIL PROTECTED]
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>
>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Regex query not using index

[Postgres User]

I'm running a simple query on 8.2.  With this syntax, Explain indicate
that the index is scanned:
select * from eod where name = 'AA'

However, when I change the query to use simple regex:
select * from eod where name ~ 'AA'

now Explain indicates a seq scan:
Index Scan using equity_eod_symbol_idx on equity_eod  (cost=0.00..8.27
rows=1 width=149)
Index Cond: ((symbol)::text = 'AA'::text)

Is there any way to 'encourage' Postgres to hit the index when using
regex?  Do I need to create a functional index or something?
Without the index in play, I really can't use regex on any of my larger tables.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

Sorry, here's the code:

CREATE TABLE table2 (
 "s_val" NUMERIC(6,2),
 "e_val" NUMERIC(6,2)
);

CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
  retval numeric(6,2);
  rec record;
begin
  SELECT * INTO rec FROM test.table2 LIMIT 0;
  rec.s_val = 100.0;
  rec.e_val = 101.0;

  -- returns correct value w/ casting:
  --retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2)) * 100;

  -- returns incorrect value, as if fields have invalid datatypes:
  retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;

  return retval;
end
$body$
LANGUAGE 'plpgsql';

On Nov 30, 2007 12:31 PM, Postgres User <[EMAIL PROTECTED]> wrote:
> Sure, I'd be glad to provide any additional code or info that I can.
> This issue took me quite awhile to track down from the 200-line
> function that was breaking.
>
> When I execute the function defined above, here's the output:
>
> select divide()
>?column?
> 
>  0
>
> > Seems rather unlikely to affect just windows. Can you post a script
> > that you can run against a blank database that shows the problem.
> > complete with output on your machine.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

Sure, I'd be glad to provide any additional code or info that I can.
This issue took me quite awhile to track down from the 200-line
function that was breaking.

When I execute the function defined above, here's what I see returned:

select divide()
?column?

 0

Hopefully someone can test on Windows to validate.


On Nov 30, 2007 8:25 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Fri, Nov 30, 2007 at 08:20:30AM -0800, Postgres User wrote:
> > tom- did you test this on wndows?  you can ignore the namespace- i'm
> > using it consistently but removed from the test code to simplify
> >
> > this problem occurs repeatedly in my code.  my guess is that it's a
> > bug in the windows build.
>
> Seems rather unlikely to affect just windows. Can you post a script
> that you can run against a blank database that shows the problem.
> complete with output on your machine.
>
>
> Have a nice day,
> --
> Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> > Those who make peaceful revolution impossible will make violent revolution 
> > inevitable.
> >  -- John F Kennedy
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHUDmUIB7bNG8LQkwRAvNMAKCQl8+bPo3bca/a33T+WVfQ/Ng2yQCdG+H5
> wZyc/alsznWsWck20lheR00=
> =RVrJ
> -END PGP SIGNATURE-
>
>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

tom- did you test this on wndows?  you can ignore the namespace- i'm
using it consistently but removed from the test code to simplify

this problem occurs repeatedly in my code.  my guess is that it's a
bug in the windows build.

On Nov 30, 2007 8:13 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> > On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
> >> However, I have found that my record variable is not assigned proper
> >> field-level datatypes.  As a result, I'm unable to write basic math
> >> calcs in pg/sql without a lot of typecasting.
>
> > What version are you running. On my 8.1.9 test system it returns -1.00
> > as expected.
>
> Works for me too, in all branches back to 8.0.  However, I noticed that
> the test function references "test.table2" not just "table2", which
> makes me wonder if maybe this is picking up some other table2 than the
> OP thinks.  A test.table2 with integer columns would explain the result.
>
>regards, tom lane
>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

8.2  /  Windows   (a development-only pc)

On Nov 30, 2007 12:50 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
> > However, I have found that my record variable is not assigned proper
> > field-level datatypes.  As a result, I'm unable to write basic math
> > calcs in pg/sql without a lot of typecasting.
>
> What version are you running. On my 8.1.9 test system it returns -1.00
> as expected.
>
> Have a nice day,
> --
> Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> > Those who make peaceful revolution impossible will make violent revolution 
> > inevitable.
> >  -- John F Kennedy
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHT87KIB7bNG8LQkwRAuzqAJ9jdaDv/rxz5pG8bdYvO9suxZLGZACeL6BY
> ZMvLJ5nKREBIsBrdk4nE748=
> =/aEm
> -END PGP SIGNATURE-
>
>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

According to the docs, record variables "take on the actual row
structure of the row they are assigned during a SELECT or FOR
command."

However, I have found that my record variable is not assigned proper
field-level datatypes.  As a result, I'm unable to write basic math
calcs in pg/sql without a lot of typecasting.

When I execute the function below, a basic math statement fails unless
I explicitly typecast the record's field values.  This isn't what I
expected; Postgresql should correctly typecast each field in the
record var automatically at the SELECT statement.
(Note: did not test with a row variable, and I prefer to use the
record datatype)

CREATE TABLE table2 (
  "s_val" NUMERIC(6,2),
  "e_val" NUMERIC(6,2)
);

CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
   retval numeric(6,2);
   rec record;
begin
   SELECT * INTO rec FROM test.table2 LIMIT 0;
   rec.s_val = 100.0;
   rec.e_val = 101.0;

   -- returns correct value w/ casting:
   --retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2)) * 100;

   -- returns incorrect value, as if fields have invalid datatypes:
   retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;

   return retval;
end
$body$
LANGUAGE 'plpgsql';

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Simple math statement - problem

[Postgres User]

The problem turned out to be related to my function..

Given this table:

CREATE TABLE "table2" (
  "s_val" numeric(6,2),
  "e_val" numeric(6,2)
) WITH OIDS;

The following functions of code will set retval = NULL;

declare
   retval numeric(6,2);
   rec record;
begin
   SELECT * INTO rec FROM table2 LIMIT 0;
   rec.s_val = 100;
   rec.e_val = 101;
   retval = (rec.s_val - rec.e_val) / rec.s_val;

   return retval;
end

However, if I explicitly typecast, then it returns the proper value:
retval = (rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2);

On Nov 29, 2007 9:47 PM, Gregory Williamson
<[EMAIL PROTECTED]> wrote:
>
>
> A quick experiment shows that if either numerator or denominator are
> decimal, that is preserved in the end result. Probably true for basic math
> operations in general.
>
> GW
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Gregory Williamson
> Sent: Thu 11/29/2007 10:37 PM
> To: Postgres User; pgsql-general
> Subject: Re: [GENERAL] Simple math statement - problem
>
> The question:
> >
> > How can I write statements that returns a decimal?
> >
> >
> billing=# select 1/100;
>  ?column?
> --
> 0
> (1 row)
>
> As you said ...
>
> So make everything decimal:
> billing=# select 1.0/100.0;
> ?column?
> 
>  0.0100
>
> Or:
> billing=# select 1::decimal/100::decimal;
> ?column?
> 
>  0.0100
>
> I think that when you use integers you lose precision right out the gate.
> Others can provide better insight I hope ...
>
> HTH,
>
> Greg Williamson
> Senior DBA
> GlobeXplorer LLC, a DigitalGlobe company
>
> Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
> (My corporate masters made me say this.)
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Gregory Williamson
> Sent: Thu 11/29/2007 10:37 PM
> To: Postgres User; pgsql-general
> Subject: Re: [GENERAL] Simple math statement - problem
>
> The question:
> >
> > How can I write statements that returns a decimal?
> >
> >
> billing=# select 1/100;
>  ?column?
> --
> 0
> (1 row)
>
> As you said ...
>
> So make everything decimal:
> billing=# select 1.0/100.0;
> ?column?
> 
>  0.0100
>
> Or:
> billing=# select 1::decimal/100::decimal;
> ?column?
> 
>  0.0100
>
> I think that when you use integers you lose precision right out the gate.
> Others can provide better insight I hope ...
>
> HTH,
>
> Greg Williamson
> Senior DBA
> GlobeXplorer LLC, a DigitalGlobe company
>
> Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
> (My corporate masters made me say this.)
>
>
>
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Simple math statement - problem

[Postgres User]

I have a large function that's doing a number of calcs.  The final
return value is wrong for a simple reason:  any division statement
where the numerator is less than the denominator is returning a zero.

Each of these statements return a 0, even when properly cast:

select 1/100
select Cast(1 / 100 As decimal)
select Cast(1 / 100 As numeric(6,2))

How can I write statements that returns a decimal?


The problem doesn't appear to be that Postgres won't return decimal
values, as these statements return the correct value:

select .01
select Cast(.01 As decimal)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] How to extract a substring using Regex

[Postgres User]

On 8/23/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 23, 2007, at 20:01 , Postgres User wrote:
>
> >>> Yes, I read the manual.  I think I had a problem because of the
> >>> special chars (< / >) that I'm trying to search for...  Still
> >>> looking
> >>> for the right syntax.
> >>
> >> Why don't you show us what you've tried and the errors you're
> >> getting? That way we can help you figure out what you're doing wrong
> >> rather than just give you an answer.
> >>
> >> Michael Glaesemann
> >
> > SELECT substring(data_field from '(.)')
> > FROM myTable
>
> Looks like you might want to brush up on regular expressions in
> general. Your expression will match a single character between the
>  tags. You might want to try something like .+ instead.
>
> Michael Glaesemann

You're right, that was a typo, I didn't copy and paste.
I found the problem, I was using 2 forward slashes instead of a
backslash + forward slash when pattern matching.  The correct regex to
extract my substring:

substring(data_field from '(.+)<\/name>')

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Simple Regex question, hoping for direct answer (no Socratic approach necessary)

[Postgres User]

I'm new to Regex in Postgres.  Can someone give me a quick pointer on
how I'd SELECT the substring between   ''and  ''  in
a field?

Sample field data:
address city here Rogers, Jim zip code place

and I'd like the SELECT to return only:
Rogers, Jim

Thanks!

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] How to extract a substring using Regex

[Postgres User]

> > Yes, I read the manual.  I think I had a problem because of the
> > special chars (< / >) that I'm trying to search for...  Still looking
> > for the right syntax.
>
> Why don't you show us what you've tried and the errors you're
> getting? That way we can help you figure out what you're doing wrong
> rather than just give you an answer.
>
> Michael Glaesemann

SELECT substring(data_field from '(.)')
FROM myTable

---(end of broadcast)---
TIP 1: 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: [GENERAL] How to extract a substring using Regex

[Postgres User]

Yes, I read the manual.  I think I had a problem because of the
special chars (< / >) that I'm trying to search for...  Still looking
for the right syntax.

On 8/23/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 23, 2007, at 19:33 , Postgres User wrote:
>
> > I'm new to Regex in Postgres.  Can someone give me a quick pointer on
> > how I'd SELECT the substring between   ''and  ''  in
> > a field?
>
> Check out regexp_replace:
>
> http://www.postgresql.org/docs/8.2/interactive/functions-
> matching.html#FUNCTIONS-POSIX-REGEXP
>
> One of the forms of substring might work for you, too.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

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

   http://archives.postgresql.org/

[GENERAL] How to extract a substring using Regex

[Postgres User]

Hi,

I'm new to Regex in Postgres.  Can someone give me a quick pointer on
how I'd SELECT the substring between   ''and  ''  in
a field?

Sample field data:
address city here Rogers, Jim zip code place

and I'd like the SELECT to return only:
Rogers, Jim

Thanks!

---(end of broadcast)---
TIP 1: 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

[GENERAL] How to join materalized view to child tables

[Postgres User]

Hi,

I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.

The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields.  This
approach is much faster than querying the separate tables and trying
to correlate and sort the results.

materialized view -  view_a
child tables   -  table_a, table_b, table_c

Here's my question- what's the fastest what to retrieve rows from each
of the child tables after I get results from view_a ?
I don't like using temp tables in Postgres (too much pain in the
past), so first selecting into a temp table which could subsequently
be joined against the child tables isn't appealing to me.

The result set from materialized view_a will never exceed 60 rows, so
I'm thinking about this:
a) LOOP on a SELECT FROM view_a
b) for each record, add the row id to one of 3 comma delimited strings
(one per child table)
c) perform a SELECT WHERE IN (delimited_string) from each child table

Any comments?  I guess I'm mainly concerned about the speed of the FOR
SELECT LOOP...

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

  http://archives.postgresql.org/

[GENERAL] Fastest way to join materalized view to child tables

[Postgres User]

Hi,

I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.

The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields.  This
approach is much faster than querying the separate tables and trying
to correlate and sort the results.

materialized view -  view_a
 child tables   -  table_a, table_b, table_c

Here's my question- what's the fastest what to retrieve rows from each
of the child tables after I get results from view_a ?
I don't like using temp tables in Postgres (too much pain in the
past), so first selecting into a temp table which could subsequently
be joined against the child tables isn't appealing to me.

The result set from materialized view_a will never exceed 60 rows, so
I'm thinking about this:
a) LOOP on a SELECT FROM view_a
b) for each record, add the row id to one of 3 comma delimited strings
(one per child table)
c) perform a SELECT WHERE IN (delimited_string) from each child table

Any comments?  I guess I'm mainly concerned about the speed of the FOR
SELECT LOOP...

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] General search problem - challenge

[Postgres User]

I have a table of around 6,000 places in the world.  Everytime my
server receives a ping, I'm grabbing the content of an article from an
RSS feed.  Then I search the article for the presence of any the 6000
terms.
A typical article is around 1200 words.

I don't need to save the article in a table and the search is
performed only once, so it's not about FTS.

Any thoughts on the best way to execute these searches using a
traditional language like C++ ?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Do Postgres exceptions rise up the stack?

[Postgres User]

Where func A, B, C, and D all update the db.  If an EXCEPTION is raised
in func D(), will all the transactions in the other children be rolled
back?
Or do I need to add code to enable this?

On 6/30/07, Postgres User <[EMAIL PROTECTED]> wrote:

How about this scenario:

func A()

begin
  x  =  func B();
  y  =  func C();

  z = func D();

end

Where func A, B, C, and D all update the db.  If a funciton is raised
in func D(), will all the transactions in the other children be rolled
back?
Or do I need to add code to enable this?


On 6/30/07, Wiebe Cazemier <[EMAIL PROTECTED]> wrote:
> On Saturday 30 June 2007 23:14, Postgres User wrote:
>
> > A basic question about raising exceptions in Postgres:
> >
> > If  Function A  calls Function B
> >
> > and Func B raises an exception, will the exception roll back the
> > transaction in Func A by default?   Or do I need to trap and re-raise
> > the exception in Func A?
> >
> > Thanks.
>
> Any exception aborts the transaction. That's how exceptions work. If you don't
> catch them, they bubble all the way to the surface. Otherwise it would be too
> much like if-statement error checking.
>
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Do Postgres exceptions rise up the stack?

[Postgres User]

How about this scenario:

func A()

begin
  x  =  func B();
  y  =  func C();

  z = func D();

end

Where func A, B, C, and D all update the db.  If a funciton is raised
in func D(), will all the transactions in the other children be rolled
back?
Or do I need to add code to enable this?


On 6/30/07, Wiebe Cazemier <[EMAIL PROTECTED]> wrote:

On Saturday 30 June 2007 23:14, Postgres User wrote:

> A basic question about raising exceptions in Postgres:
>
> If  Function A  calls Function B
>
> and Func B raises an exception, will the exception roll back the
> transaction in Func A by default?   Or do I need to trap and re-raise
> the exception in Func A?
>
> Thanks.

Any exception aborts the transaction. That's how exceptions work. If you don't
catch them, they bubble all the way to the surface. Otherwise it would be too
much like if-statement error checking.


---(end of broadcast)---
TIP 6: explain analyze is your friend



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] SQL problem..

[Postgres User]

Your query won't work because there is no single Price associated with
a range of dates.  It doesn't make sense.

Do you mean to select AVG(Price)?

On 6/28/07, Bauhaus <[EMAIL PROTECTED]> wrote:

Hello,

I'm an Access/SQL novice and I have an sql problem:

I have the following table Price:

FuelID PriceDate   Price
LPG1/05/2007   0,2
LPG13/05/2007 0,21
SPS 2/05/2007   1,1
SPS 15/05/2007 1,08

And I have to make the following query:

FuelID PriceDate_from PriceDate_To Price
LPG1/05/2007 13/05/2007 0,2
SPS 2/05/2007 15/05/20071,1
LPG13/05/2007  0,21
SPS 15/05/2007 1,08

I tried this:

SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS
PriceDate_to FROM Price GROUP BY FuelID;

Problem is, when I put Price in the select, I get the error 'Price not part
of an aggregate function' :s
Eitherway, it doesnt work, I only have one FuelDate_from and one FuelDate_to
if I use min & max. While there should be several from...to... dates for a
particular fuel.

How can I solve this ?



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

  http://archives.postgresql.org/



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Do Postgres exceptions rise up the stack?

[Postgres User]

A basic question about raising exceptions in Postgres:

If  Function A  calls Function B

and Func B raises an exception, will the exception roll back the
transaction in Func A by default?   Or do I need to trap and re-raise
the exception in Func A?

Thanks.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] newid() in postgres

[Postgres User]

You'll need to create a custom function in Postgres to support this,
which is fairly easy.  It's been done before- do a search on Google:

http://www.hclausen.net/psql.php

On 5 Apr 2007 01:27:15 -0700, marcel.beutner <[EMAIL PROTECTED]> wrote:

Hello,

Thanks a lot for your answers! But I don't need a sequence which only
will be incremented. I need a _real_ GUID just as the newid()
function. Is there no way to generate such a GUID?

I need a real GUID because I use them further in my host app. And my
host app relies on it.


Thanks for your answers.

Marcel


---(end of broadcast)---
TIP 6: explain analyze is your friend



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Using C# to create stored procedures

[Postgres User]

Andrus,

As a C# developer myself, I'd recommend learning pl/pg sql for
writring stored procs.  It's designed specifically for the kinds of
iterations and other operations you need when hanlding a recordset.
It's nothing like transact-sql of SQL Server.

If you must have full C# integration, then you're better off with SQL
Server 2005.

By the way, .NET LINQ will make most of your current thinking
irrelevant in about 12 months.

On 4/2/07, Andrus <[EMAIL PROTECTED]> wrote:

Last change for this project was 3 years ago.
So I think that it is dead.

I'm writing application in C#.
I expected that I can wrote stored procedures in C# also using something
like mod_mono  in Apache.

So it seems that most reasonable way is to learn dreaded plpgsql language
and write
stored procedures in it.

Andrus.


>> Any idea how to write server-side stored procedures in C#  for
>> PostgreSQL database ?
>
> There's an old project called PL/Mono
> , but as
> far as I know it's unmaintained.  You might want to try to contact the
> author. :)
>
> Cheers,
> David.
>
>> In windows .NET 2 framework should be used and in Linuc/Mac/Windows
>> MONO should be used for this.
>>
>> How to install MONO engine as server-side language to PostgreSQL ?
>>
>> How to call .NET dlls from PostgreSQL stored procedure ?
>
>>
>> Andrus.
>>
>>
>> ---(end of broadcast)---
>> TIP 1: 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
>
> --
> David Fetter <[EMAIL PROTECTED]> http://fetter.org/
> phone: +1 415 235 3778AIM: dfetter666
>  Skype: davidfetter
>
> Remember to vote!
> Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>


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

  http://archives.postgresql.org/



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Granting permissions

[Postgres User]

Is there a way to grant INSERT and UPDATE permissions on all the
tables in a database?   I don't want to type-in every table name...

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Using MS Access front-end with PG]

[Postgres User]

Oleg,

This looks like a great module, do you have a pointer to it in English?

If can send this module to me as a compressed file, I'll take the time
to post it on PgFoundry as a new project that everyone can easily
access and download.

Paul- if you go with the lower() edits route, be sure to note Joshua's
coment on the funcional index- one of the best, unknown features in
PG.

On 4/3/07, Oleg Bartunov  wrote:

Paul,

we have contrib module mchar, which does what you need. We developed it
when porting from MS SQL one very popular in Russia accounting software.
It's available from http://v8.1c.ru/overview/postgres_patches_notes.htm,
in Russian. I don't rememeber about license, though.


Oleg

On Wed, 4 Apr 2007, Paul Lambert wrote:

> Joshua D. Drake wrote:
>
>>
>> You could preface all your queries with something like:
>>
>> select * from foo where lower(bar) = lower('qualifer');
>>
>> But that seems a bit silly.
>>
>> Joshua D. Drake
>>
>>
>
> I'm trying to avoid having to alter all of my queries, per the OP I've got
> several hundred if not thousands of them and if I have to change them all to
> put lower() around all the text, that is a lot of time.
>
> If I have to do that I will, I'm just curious if there was an ability to tell
> pg to not be case sensitive when doing lookups.
>
> Judging by the responses so far, there is not... so I'll get to work :)
>
>

   Regards,
   Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 6: explain analyze is your friend



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Storing blobs in PG DB

[Postgres User]

I recently heard from Josh Berkus that at least one major CMS
application uses Postgres to store entire HTML pages (including image
files) in order to support full versioning.

As a general rule, I prefer not to store BLOBS in a DB- I'd rather
leave the BLOB in the file system and let the db save only a pointer
to it.  However, if you need to store BLOBs in a database, Postgres is
one of the better platforms for it.

On 4/3/07, Nikolay Moskvichev <[EMAIL PROTECTED]> wrote:

Hi All!

Question is : How suitable PG for storing about 2 000 000 binary files
0,5-2,0 Mb size each ? It is not planned the big number of clients or a
plenty of updatings. Like photoalbum on local host.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq



---(end of broadcast)---
TIP 1: 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: [GENERAL] Need your help on using "partion"

[Postgres User]

My guess is that when you insert into Cities, the sequence field is
incremented one time (=2), and when the Rule is executed to insert
into Capital, the sequence is incremented a second time (=3).  As a
result of these 2 operations, the sequence is incremented 2 times.

I don't know anything about your application or business rules, but
your solution seems unnecessarily complicated.  Remember, the best and
smartest approach is usually the simplest one.  You can do this in a
much simpler way.

On 4/4/07, Huynh Ngoc Doan <[EMAIL PROTECTED]> wrote:


/*I'm learning how to use the partion in Postgres 8.2.3 so I want to
do some test for my awareness.
I create 2 tables:
The parent table is cities:*/
CREATE TABLE xxx.cities
(
  id serial NOT NULL,
  name text,
  population real,
  altitude integer,
  CONSTRAINT pk_cities PRIMARY KEY (id)
)
/*and a partion of cities is capital:*/
CREATE TABLE xxx.capital
(
  CONSTRAINT pk_capital PRIMARY KEY (id),
  CONSTRAINT capital_id_check CHECK (id > 3)
) INHERITS (xxx.cities)

/*My purpose is that the cities will contain the rows having id <= 3
and the capital will contain the rows having id >=3.
So I create a rule cities_insert_capital to redirect data to the
table capital when new row's id >3
Here it is:
*/
CREATE OR REPLACE RULE cities_insert_capital AS
ON INSERT TO xxx.cities
   WHERE new.id > 3 DO INSTEAD INSERT INTO xxx.capital(id,name,
population, altitude) VALUES (new.id,new.name, new.population, new.altitude)

After completing the reparation stage, I insert some rows into cities table:
--restart the serial value to 1
alter sequence xxx.cities_id_seq restart with 1;
--serial is 1 at this time
--1.
INSERT INTO xxx.cities(name, population, altitude)
VALUES('HCM',10,10);
--data is inserted into cities table as the result, serial is 3,and
the expected value is 2 =>what wrong here?
--2.Try more insert
INSERT INTO xxx.cities(name, population, altitude)
VALUES('Ha Noi',10,10);
--data is inserted into capital while it's is expected to insert to cities.
--serial is 5 at this time,and expected value is 3 => what wrong here?

/*Conclusion:
The serial increases by 2 when excuting one insert command.I try many
ways to find out the reason why, but I can't.
Can you help me to explain this unexpected behavior and give me some
advices to deal with this problem.
I want that the serial will increase by 1 unit after a insert comand.
Thank you very much.
Note:
I want id will be add automatically when a row is inserted.
*/
Best regard,
Doan. From VietNam


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] newid() in postgres

[Postgres User]

Marcel,

A sequence represents a unique identifier. You can call the function
'nextval' to get the next unique value in the sequence.  See related
functions here:

http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html

In this code, I get the next sequence, insert it into a table, and
then return the value to the calling function:

DECLARE
nextseq integer;

BEGIN
   nextseq := nextval('entry_id_seq');

INSERT INTO my_table (
entry_id,
entry_text,
) VALUES (
nextseq,
p_entry_text,  -- input param
);

On 4/4/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote:

marcel.beutner wrote:
> I've searched in the groups already, but couldn't find any helpful
> information - only to use a sequence, which returns just a number and
> not a unique identifier.

Which properties do your unique identifiers posses that are not
satisfied by a number returned by a sequence?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq



---(end of broadcast)---
TIP 1: 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: [GENERAL] How to capture and handle failed INSERT

[Postgres User]

The best answer: RTFM!

I found this example in the docs, although it should really exit the
Loop after 10 failed attempts instead of going indefinitely:

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
   LOOP
   UPDATE db SET b = data WHERE a = key;
   IF found THEN
   RETURN;
   END IF;

   BEGIN
   INSERT INTO db(a,b) VALUES (key, data);
   RETURN;
   EXCEPTION WHEN unique_violation THEN
   -- do nothing
   END;
   END LOOP;
END;
$$
LANGUAGE plpgsql;


On 3/4/07, Berend Tober <[EMAIL PROTECTED]> wrote:

Postgres User wrote:
> I'm using this code to increment a counter table:
>
> IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
>UPDATE counter_tbl SET  counter_fld = counter_fld + 1
>  WHERE key_fld = 'key_val';
>  ELSE
>INSERT INTO counter_tbl(key_fld, counter_fld) VALUES('key_val', 1);
> END IF;
>
> Now, I assume that it's possible for another session to INSERT a row
> in the microseconds that exist between the Select and Insert
> statements above.
>
> I also assume that I can wrap the above code in a transaction, and if
> the transaction fails (because another session's Insert causes my
> Insert to fail), then I simply need to re-execute it once. (Updates
> should never fail.)
>
> Does anyone have a simple example of the best way to code this type of
> transaction- and the best way to re-execute the same code on failure?
> I could use a loop but I'm not sure if that's the best solution.

I think I would try the INSERT first. If it fails, then trap the
exception and do the UPDATE




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

  http://archives.postgresql.org/

[GENERAL] How to capture and handle failed INSERT

[Postgres User]

Hi,

I'm using this code to increment a counter table:

IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
   UPDATE counter_tbl SET  counter_fld = counter_fld + 1
 WHERE key_fld = 'key_val';
 ELSE
   INSERT INTO counter_tbl(key_fld, counter_fld) VALUES('key_val', 1);
END IF;

Now, I assume that it's possible for another session to INSERT a row
in the microseconds that exist between the Select and Insert
statements above.

I also assume that I can wrap the above code in a transaction, and if
the transaction fails (because another session's Insert causes my
Insert to fail), then I simply need to re-execute it once. (Updates
should never fail.)

Does anyone have a simple example of the best way to code this type of
transaction- and the best way to re-execute the same code on failure?
I could use a loop but I'm not sure if that's the best solution.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Add items to a record variable

[Postgres User]

The first hack is probably a little easier.  Let me try and flesh it
out for you:

declare
   rec1   record;
   rec2   record;
   new_fields  varchar = '';

begin
  select fieldlist from mytable into rec1;
  -- inspect and play with your rec1 here
  -- now start a loop to add new fields

 if new_fields != '' then
new_fields = new_fields || ', '
 end if;

  new_fields = new_fields || new field value here || '::' || new
field type here;

  -- close loop here

  -- now select all rows into a new record variable

  execute 'select mt.fieldlist ' || new_fields || ' from mytable mt into rec2';

end;

This is a hack but it should work.  You'll end up with a single record
var with your old fields and new fields, typed as you have specified.


On 3/1/07, Eddy D. Sanchez <[EMAIL PROTECTED]> wrote:

Good idea, thanks a lot, I try it

On Mar 2, 2007, at 1:32 AM, Postgres User wrote:

> Ok, you can always SELECT into a temp table.  If you want to add
> additional fields in a loop, you can call an Execute 'Alter Table Add
> Column'
> to add all the fields you need.  Then call Update to insert values
> into the fields.
>
> Finally do a select into your record var.
>
> On 3/1/07, Eddy D. Sanchez <[EMAIL PROTECTED]> wrote:
>> Thanks.
>>
>> But I need to add fields to a defined and filled record, I don't know
>> if I can do it, and how can I do it.
>>
>> If I have:
>>
>> v_record record;
>>
>> EXECUTE 'select * from table'
>> INTO v_record;
>>
>> supposing (after query) that v_record contains just one row with 5
>> fields, I need to add the 6th, 7th, 8th fields, etc, etc.
>>
>> Do you understand ?
>>
>>
>>
>> On Mar 2, 2007, at 12:48 AM, Postgres User wrote:
>>
>> > If you need to return a record to another function or client
>> program,
>> > you can always use this sytnax:
>> >
>> > SELECT var_a::integer, var_b::integer, var_c::integer... etc
>> >
>> > where var_a, var_b, etc and local variables that hold your
>> > calculated values.
>> >
>> > You dont need to create a local record structure- select will do
>> that
>> > for you.  The '::integer' will ensure that the values are
>> typecast as
>> > integers.
>> >
>> >
>> > On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
>> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>> >>
>> >> Thank you for you help
>> >>
>> >> The item is a field, I need to return a record with double
>> >> precision or integer
>> >> fields.
>> >>
>> >> I need a function that can calculate some fields (integer numbers)
>> >> that should
>> >> be added to a record inside a loop, and the result, must seems
>> >> like this:
>> >>
>> >> {234.00, 56434.78, 5556.89,}
>> >>
>> >> Always, I call those functions with:
>> >>
>> >> select * from func_with_record() as (field1 integer, field2
>> >> integer, field3
>> >> integer,)
>> >>
>> >> Do you undestand?
>> >>
>> >> Thanks a lot for your interesting.
>> >>
>> >>
>> >>
>> >> ---
>> >>
>> >> What is the new item?  Is it a field?
>> >>
>> >> On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
>> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>> >>
>> >>
>> >> Can anyone help me???
>> >>
>> >> I work with plpgsql and I need to add items to a record variable,
>> >> with a for
>> >> statement, something like this:
>> >>
>> >> declare
>> >>  v_rec record;
>> >> begin
>> >> for nn in (some_xpresion) loop
>> >>  
>> >>
>> >> v_rec = vrec + [new_item]; <--Here I need to add an item to record
>> >> variable with
>> >>  each loop
>> >> end loop lp;
>> >>
>> >> return v_rec;
>> >> end;
>> >>
>> >>
>> >> The result should be like {it1,it2,it3,i4,}
>> >>
>> >> Please can you help me?
>> >> Thanks a lot
>> >>
>> >> -
>> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> >> -
>> >>
>> >> ---(end of
>> >> broadcast)---
>> >> TIP 1: 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
>> >>
>> >>
>> >>
>> >> -
>> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> >> -
>> >>
>>
>>




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

I had tried several variations of MOVE Backward inside an Execute
statement earlier.  And now, I'm seeing this error appear again:

ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL

I updated the Postgres function below with this replacement line:
Execute 'MOVE Backward All In c_entry';

Any other thoughts?


On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Postgres User" <[EMAIL PROTECTED]> writes:
> So I added the following code:

>  ref_entry = 'c_entry';
>   MOVE Backward All In c_entry;

You have to use EXECUTE for the latter.

   regards, tom lane



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

  http://archives.postgresql.org/

Re: [GENERAL] Add items to a record variable

[Postgres User]

If you need to return a record to another function or client program,
you can always use this sytnax:

SELECT var_a::integer, var_b::integer, var_c::integer... etc

where var_a, var_b, etc and local variables that hold your calculated values.

You dont need to create a local record structure- select will do that
for you.  The '::integer' will ensure that the values are typecast as
integers.


On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:

WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia

Thank you for you help

The item is a field, I need to return a record with double precision or integer
fields.

I need a function that can calculate some fields (integer numbers) that should
be added to a record inside a loop, and the result, must seems like this:

{234.00, 56434.78, 5556.89,}

Always, I call those functions with:

select * from func_with_record() as (field1 integer, field2 integer, field3
integer,)

Do you undestand?

Thanks a lot for your interesting.



---

What is the new item?  Is it a field?

On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia


Can anyone help me???

I work with plpgsql and I need to add items to a record variable, with a for
statement, something like this:

declare
 v_rec record;
begin
for nn in (some_xpresion) loop
 

v_rec = vrec + [new_item]; <--Here I need to add an item to record variable with
 each loop
end loop lp;

return v_rec;
end;


The result should be like {it1,it2,it3,i4,}

Please can you help me?
Thanks a lot

-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-

---(end of broadcast)---
TIP 1: 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



-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-



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

  http://archives.postgresql.org/

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

Thanks for the pointer.  According to the Postgres docs:

"The portal name used for a cursor can be specified by the programmer
or automatically generated. To specify a portal name, simply assign a
string to the refcursor variable before opening it."

So I added the following code:

 ref_entry = 'c_entry';
  MOVE Backward All In c_entry;

Which compiles- progress!  But when I try to execute it, this error is returned:

SPI_execute_plan failed executing query "MOVE Backward All In
c_entry": SPI_ERROR_CURSOR

No luck finding any info on this error, except for a few bug reports.
Any ideas?  I've pasted my complete code below:


DECLARE
   ref_entry refcursor;

   rec record;
   v_list varchar = '';
   i integer = 0;

BEGIN

-- assign name to cursor
ref_entry = 'c_entry';

OPEN ref_entry FOR
SELECT * FROM big_select_statement

LOOP
  FETCH ref_entry INTO rec;
  EXIT WHEN NOT FOUND;

  i = i + 1;
  IF v_list != '' THEN
 v_list = v_list || ', ';
  END IF;
  v_list = v_list || rec.entry_id::varchar;

END LOOP;
MOVE Backward All In c_entry;

END;

On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Postgres User" <[EMAIL PROTECTED]> writes:
> Is there any way to 'rewind' the cursor to the first row?

plpgsql doesn't have any command for that (though I think someone is
working on improving its cursor command set).  You should be able to
work around it by EXECUTE'ing a MOVE BACKWARD ALL command, though.
You just need to know the real name of the cursor --- see 'Returning
Cursors' in the plpgsql docs for discussion.

   regards, tom lane



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

Hi,
I'm opening a refcursor in Postgres to return a dataset to the client.
However, before returning the cursor, I'd like to iterate thru the
rows.  Here's the code:

 DECLARE
ref_entry  refcursor;
rec  record;
i  integer = 0;
v_list varchar = '';

 BEGIN
OPEN ref_entry FOR
SELECT * FROM big_select_statement;

LOOP
  FETCH ref_entry INTO rec;
  EXIT WHEN NOT FOUND;

  i = i + 1;
  IF v_list != '' THEN
 v_list = v_list || ', ';
  END IF;
  v_list = v_list || rec.entry_id::varchar;

END LOOP;

Return next ref_entry;

 END;

There's one slight and obvious problem- the cursor returns nothing to
the client because I've already fetched all the rows.  (Remove the
LOOP, and the cursor returns all rows as expected).

Is there any way to 'rewind' the cursor to the first row?  I realize
that I can simply execute the full query and open another cursor to
return to the client, but I'll take a performance hit that I'd like to
avoid.

I've tried a few FETCH BACKWARD and other statements but only get
syntax errors returned by the comiler.  Anyone done this before?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2

[Postgres User]

That's what I was afraid of... it's a new install of Win Server 2003 R2, so I can rule out any third party firewall. Windows Firewall is NOT installed.  And I've installed Postgres on a Windows XP box behind the same router, so it's not a router-firewall issue.

  
It's probably a new R2 feature, I'll ping someone at Microsoft because I don't have a clue at this point what the problem is...
 
Jon  
On 1/8/06, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> Has anyone tried to install Postgres on Windows Sever 2003> version R2?  R2 is actually shipping as a 'new' Microsoft
> product- it's basically an interim update to Windows Server (> http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.msp
> x  ).Not that I know of, but it's been on my list of things to try :-)
> I've installed Postgres on other versions of Windows with no> problem, so I'm afraid that the error I'm seeing now is> related to some great new 'feature' from Microsoft.  Here's> the error message returned by Postgres before install begins-
> Error binding the test network socket: 10013That error means: An attempt was made to access a socket in a wayforbidden by its access permissions.> Microsoft Antispyware has been turned off (closed the
> application) and Windows Firewall isn't running.  There's no> other AV or firewall software on this system yet.>> Any ideas on what might be going on?It certainly *sounds* a lot like a firewall issue :-) I'd double and
triple check that. We've seen it several times before and it has AFAIKalways been a firewall.//Magnus

[GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2

[Postgres User]

Hi,
 
Has anyone tried to install Postgres on Windows Sever 2003 version R2?  R2 is actually shipping as a 'new' Microsoft product- it's basically an interim update to Windows Server (
http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.mspx).
 
I've installed Postgres on other versions of Windows with no problem, so I'm afraid that the error I'm seeing now is related to some great new 'feature' from Microsoft.  Here's the error message returned by Postgres before install begins-

Error binding the test network socket: 10013
 
Microsoft Antispyware has been turned off (closed the application) and Windows Firewall isn't running.  There's no other AV or firewall software on this system yet.
 
Any ideas on what might be going on?
 
Jon