Re: [SQL] function array_to_string(text[]) does not exist
Hello you should to enter separator postgres=# select array_to_string(ARRAY[1,2,3,4], '|'); array_to_string ─ 1|2|3|4 (1 row) Regards Pavel Stehule 2013/8/25 Victor Sterpu > Hello > > When I run : > SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), '-', > CAST(ltrv1.val_max AS CHAR), ' ', ltrv1.comentarii))) > FROM lab_tests_reference_values ltrv1 > GROUP BY ltrv1.val_min, ltrv1.val_max, ltrv1.comentarii; > > I get the error: > ERROR: function array_to_string(text[]) does not exist > LINE 1: SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min ... >^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > I tryed to cast but it's still not working. > > Thanku you. > > *DISCLAIMER: > Acest mesaj de posta electronica si documentele aferente sunt > confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod > de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este > interzis sa actionati in baza acestor informatii. Citirea, copierea, > distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute > in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din > greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea > comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un > mod sigur si lipsit de erori de transmitere a informatiilor, este > responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele > alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs. > * > >
[SQL] Re: [SQL] Table indexes in a SELECT with JOIN´s
Hello 2013/4/20 JORGE MALDONADO > Let´s suppose that I have a SELECT statement that joins more than one > table and such a statement is order by fields that belong not only to the > table in the FROM but also by fields in the tables that are part of the > JOIN´s. How does indexes should be considered in a case like this? For > example: > > SELECT artist_name, author_name, producer_name, song_name > FROM tbl_songs > INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid > INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid > INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid > ORDER BY song_name > > It depends on size of relations - you don't need indexes on small tables - hash join will be used. For bigger tables indexes on PK (automatically) and FK are good idea. Regards Pavel > Respectfully, > Jorge Maldonado >
Re: [SQL] From with case
2013/3/26 Pavel Stehule :
> Hello
>
> 2013/3/26 Ben Morrow :
>> Quoth pavel.steh...@gmail.com (Pavel Stehule):
>>> Dne 25.3.2013 23:51 "Ben Morrow" napsal(a):
>>> >
>>> > I would use a view for this:
>>> >
>>> > create view vale_any as
>>> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
>>> > from valepag v
>>> > union all
>>> > select 'R', v.adiant, v.desc_per, v.cod
>>> > from valerec v;
>>> >
>>> > then
>>> >
>>> > for rSql in
>>> > select a.adiant, a.desc_per
>>> > from vale_any a
>>> > where a.type = cTip and a.cod = 2
>>> > loop
>>>
>>> This design has a performance problem. You read both tables everywhere -
>>> for large tables can be bad
>>
>> You would think so, but, in general, Pg is cleverer than that. For the
>> simple case of queries with constants in (so, a client-submitted query
>> like
>>
>> select * from vale_any a where a.type = 'P' and a.cod = 2
>>
>> or the equivalent with bound placeholders) the planner won't even plan
>> the parts of the view which don't get used. Try some experiments with
>> EXPLAIN to see what I mean: the unused sections of the Append (that is,
>> the UNION ALL) are either omitted entirely or get replaced with
>>
>> Result
>> One-Time Filter: false
>>
>> (I'm not entirely sure what makes the difference, though it seems to be
>> to do with how complicated the individual parts of the UNION are).
>>
>> PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
>> pre-plans all its statements, so the condition on a.type is not constant
>> at planning time. However, if you PREPARE a statement like
>>
>> prepare v as select * from vale_any a
>> where a.type = $1 and a.cod = $2
>>
>> and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
>> that although the plan includes the parts of the view that don't get
>> used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
>> the executor had enough information to work out they could never return
>> any rows. Skipping those parts of the plan at execute time does have a
>> small cost--for small tables you will see the total query time go up a
>> little for a prepared statement--but nothing like the cost of scanning a
>> large table. I would expect it's about the same as the cost of a
>> PL/pgSQL IF/THEN/ELSE.
>>
>> It's worth noting at this point that if you know the rows of a UNION
>> will be distinct it's worth making it a UNION ALL, since otherwise Pg
>> has to add a sort-and-uniq step which can be expensive.
>>
>> Ben
>>
>
> you have a true
>
> CREATE OR REPLACE FUNCTION public.fo1(alfa integer)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> declare r record;
> begin
> for r in explain
>select * FROM (
> select 1::int as filter, * from f1
> union all
> select 2 as filter, * from f2) x
>where x.filter = alfa
> loop
> raise notice '%', r;
> end loop;
> end;
> $function$
>
> postgres=# select fo1(1);
> NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
> NOTICE: (" -> Seq Scan on f1 (cost=0.00..34.00 rows=2400 width=8)")
> fo1
> -
>
> (1 row)
>
> postgres=# select fo1(2);
> NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
> NOTICE: (" -> Seq Scan on f2 (cost=0.00..34.00 rows=2400 width=8)")
> fo1
> -
>
> (1 row)
>
> In this case is postgres smart enough (Postgres 9.3)
>
> Is strange, so this example doesn't work on 9.1. - PREPARE and EXECUTE
> works with "one time filter", but plpgsql code doesn't work - it
> returns nothing
>
> Regards
>
> Pavel Stehule
just one note - it works on 9.1. well - my mistake - tested on
different server with different client_min_messages.
Regards
Pavel
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] From with case
Hello
2013/3/26 Ben Morrow :
> Quoth pavel.steh...@gmail.com (Pavel Stehule):
>> Dne 25.3.2013 23:51 "Ben Morrow" napsal(a):
>> >
>> > I would use a view for this:
>> >
>> > create view vale_any as
>> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
>> > from valepag v
>> > union all
>> > select 'R', v.adiant, v.desc_per, v.cod
>> > from valerec v;
>> >
>> > then
>> >
>> > for rSql in
>> > select a.adiant, a.desc_per
>> > from vale_any a
>> > where a.type = cTip and a.cod = 2
>> > loop
>>
>> This design has a performance problem. You read both tables everywhere -
>> for large tables can be bad
>
> You would think so, but, in general, Pg is cleverer than that. For the
> simple case of queries with constants in (so, a client-submitted query
> like
>
> select * from vale_any a where a.type = 'P' and a.cod = 2
>
> or the equivalent with bound placeholders) the planner won't even plan
> the parts of the view which don't get used. Try some experiments with
> EXPLAIN to see what I mean: the unused sections of the Append (that is,
> the UNION ALL) are either omitted entirely or get replaced with
>
> Result
> One-Time Filter: false
>
> (I'm not entirely sure what makes the difference, though it seems to be
> to do with how complicated the individual parts of the UNION are).
>
> PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
> pre-plans all its statements, so the condition on a.type is not constant
> at planning time. However, if you PREPARE a statement like
>
> prepare v as select * from vale_any a
> where a.type = $1 and a.cod = $2
>
> and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
> that although the plan includes the parts of the view that don't get
> used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
> the executor had enough information to work out they could never return
> any rows. Skipping those parts of the plan at execute time does have a
> small cost--for small tables you will see the total query time go up a
> little for a prepared statement--but nothing like the cost of scanning a
> large table. I would expect it's about the same as the cost of a
> PL/pgSQL IF/THEN/ELSE.
>
> It's worth noting at this point that if you know the rows of a UNION
> will be distinct it's worth making it a UNION ALL, since otherwise Pg
> has to add a sort-and-uniq step which can be expensive.
>
> Ben
>
you have a true
CREATE OR REPLACE FUNCTION public.fo1(alfa integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare r record;
begin
for r in explain
select * FROM (
select 1::int as filter, * from f1
union all
select 2 as filter, * from f2) x
where x.filter = alfa
loop
raise notice '%', r;
end loop;
end;
$function$
postgres=# select fo1(1);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f1 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-
(1 row)
postgres=# select fo1(2);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f2 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-
(1 row)
In this case is postgres smart enough (Postgres 9.3)
Is strange, so this example doesn't work on 9.1. - PREPARE and EXECUTE
works with "one time filter", but plpgsql code doesn't work - it
returns nothing
Regards
Pavel Stehule
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] From with case
This design has a performance problem. You read both tables everywhere - for large tables can be bad Dne 25.3.2013 23:51 "Ben Morrow" napsal(a): > > Quoth c...@sygecom.com.br (Mauricio Cruz): > > > > I'm working in a PL/SQL and I'd like to use the same > > PL for 2 kinds of tables... > > > > I have "valepag" and "valerec" both tables > > have the same columns, but one is for debit and the other one is for > > credit, the PL will work for both cases > > > > with the unique diference for > > the name of the table... > > > > So I thought to use something like this: > > ... > > > > For rSql in select a.adiant, > > a.desc_per > > from case > > when > > cTip='P' > > then valapag > > else valerec > > end > > where cod=2 Loop > > > > ... > > > > But > > it just dont work... does some one have other solution for this case ? > > I would use a view for this: > > create view vale_any as > select 'P'::text "type", v.adiant, v.desc_per, v.cod > from valepag v > union all > select 'R', v.adiant, v.desc_per, v.cod > from valerec v; > > then > > for rSql in > select a.adiant, a.desc_per > from vale_any a > where a.type = cTip and a.cod = 2 > loop > > You need to cast the constant in the view definition, otherwise Pg > complains about its type being ambiguous. You should use the same type > as cTip will be. > > Ben > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] From with case
Hello
> For rSql in select a.adiant,
>a.desc_per
> from case
> when cTip='P'
> then valapag
> else valerec
>end
> where cod=2 Loop
you can use a dynamic SQL, but it is not best solution usually. In
this case I usually prefer
IF cTip = 'P' THEN
FOR r IN SELECT .. FROM valapag LOOP
PERFORM proc(r);
END LOOP;
ELSE
FOR r IN SELECT .. FROM valerec LOOP
PERFORM proc(r);
END LOOP;
END IF;
with dynamic SQL
FOR r IN EXECUTE format('SELECT .. FROM %I ..', CASE WHEN ctip = 'P'
THEN 'valapag' ELSE 'valerec' END)
LOOP
..
END LOOP;
Regards
Pavel Stehule
2013/3/25 Mauricio Cruz :
> Hi everyone,
>
>
>
> I'm working in a PL/SQL and I'd like to use the same PL for 2 kinds of
> tables...
>
> I have "valepag" and "valerec" both tables have the same columns, but one is
> for debit and the other one is for credit, the PL will work for both cases
>
> with the unique diference for the name of the table...
>
>
>
> So I thought to use something like this:
>
> ...
>
> For rSql in select a.adiant,
>a.desc_per
> from case
> when cTip='P'
> then valapag
> else valerec
>end
> where cod=2 Loop
>
>
>
> ...
>
>
> But it just dont work... does some one have other solution for this case ?
>
>
>
> Thanks guys.
>
>
>
> --
> Grato,
> Mauricio Cruz
> Sygecom Informática
> 51 3442-3975 / 3442-2345
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] xmlelement name
Hello
2013/3/12 Ben Morgan :
> Hi,
>
> I'm trying to write a function that will take a name as a text value,
> and return an XML element with that name as name, like so:
>
> create function xpercent(nam text, val int) returns xml as $$
> begin
> return ( select xmlelement(name nam, concat(val::text, '%')) );
> end;
> $$ language plpgsql;
you cannout use parameter there - Name of xmlttribute is constant, it
should be immutable
you have to use dynamic sql
CREATE OR REPLACE FUNCTION public.xpercent(nam text, val integer)
RETURNS xml
LANGUAGE plpgsql
AS $function$
declare result text;
begin
execute format('SELECT xmlelement(name %I, $1)', nam) USING
concat(val::text, '%') INTO result;
return result;
end;
$function$
postgres=# select xpercent('hello', 4);
xpercent
---
4%
(1 row)
Regards
Pavel Stehule
>
> But when I call the function, nam is used as the name instead of what
> the parameter nam contains:
>
> select xpercent('hello', 4);
>xpercent
> ---
> 4%
> (1 row)
>
> How can I get this to work so I get 4% Thanks!
>
> –Ben
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to put multiples results in just one column
2013/1/31 Nei Rauni Santos :
> Thank you Pavel,
>
> I could do that like this:
>
>
> select p.id,
>
> ( select array_accum ((
> room_name, room_id, room_group_name, room_group_id, room_order,
> availability_min, price_amount, price_min, price_avg, price_balcony_amount,
> price_balcony_avg, capacity, deposit_required, breakfast_included,
> room_min_stay
> )::cms.room_availability_list_type)
> from cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-05',
> 'pt_BR', 1, '{1}')
> ) room
> from wr.prestadores p
> where p.id = 2;
>
> the only problem is that it takes about 2293 ms for just one result.
>
>
> Any idea about the more effective way to do that?
you have to check queries inside function and you have to find slow
query and try to solve it.
http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions
one note - in your function there is lot of repeated queries to table
cms.room_availability_list - if this table is not small, then a
function cannot be super fast. A art of writing stored procedures is
in minimizing reading from large tables.
Regards
Pavel
>
>
>
>
>
> On Thu, Jan 31, 2013 at 10:39 AM, Pavel Stehule
> wrote:
>>
>> fce
>
>
>
>
>
> --
> []s!!
>
> Nei
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to put multiples results in just one column
Hello
select (fce(..)).column from ...
or select column from fce()
Regards
Pavel Stehule
2013/1/31 Nei Rauni Santos :
> Hi,
>
> The problem is, I'm working in a list of hotels which should have
> availability of rooms and list the hotel and its rooms on the application.
>
> I have this function which already is used to get the rooms available
> select cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-02',
> 'pt_BR', 1, '{1}')
>
> which result is a list of rooms ( type ) for a specific hotel.
>
> ("Apartment single",2117,"Apartamento
> Superior",1681,4,10,100.00,100.00,100.00,127.32,127.32,3,1,{1},)"
> "("Apartment single 2",4981,"Apartamento
> Superior",1681,6,10,100.00,100.00,100.00,149.80,149.80,5,1,{1},)"
> "("Apartment double",13862,"Apartamento
> Luxo",4311,11,10,100.00,100.00,100.00,107.99,107.99,2,1,{1},)"
> "("Suite double",13867,"Suíte
> Executiva",4313,15,10,100.00,100.00,100.00,174.32,174.32,1,1,{1},)
>
> I need to get one result of hotel's table and a way to return all the rows
> available in a single column as a array with all the data showed below.
>
> Is that possible?
>
> thank you,
>
>
> Follow my function responsable to filter rooms available:
>
> CREATE OR REPLACE FUNCTION cms.sp_get_supplier_availability(in_supplier_id
> integer, in_checkin date, in_checkout date, in_culture character varying,
> in_room_qty integer, in_people_qty integer[])
> RETURNS SETOF cms.room_availability_list_type AS
> $BODY$ DECLARE
> i INTEGER;
> AVAIL INTEGER[];
> DIFF_DAYS INTEGER;
> _room_availability cms.room_availability_list_type%rowtype;
> _room RECORD;
> BEGIN
>
> IF( supplier.available_for_booking( in_supplier_id ) IS FALSE ) THEN
> RETURN ;
> END IF;
>
> -- release
> IF(cms.sp_supplier_release(in_supplier_id, in_checkin) IS FALSE) THEN
> RETURN ;
> END IF;
>
> IF (cms.sp_supplier_is_unavailable(in_supplier_id, in_checkin,
> in_checkout)) THEN
> RETURN ;
> END IF;
>
>
> DIFF_DAYS := (IN_CHECKOUT - IN_CHECKIN);
> i:=0;
> AVAIL := NULL;
>
> IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
> SELECT array_accum(ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE;
> ELSE
> IF IN_PEOPLE_QTY IS NULL AND IN_ROOM_QTY IS NOT NULL THEN
> SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE GROUP BY ra.day HAVING
> sum(ra.room_real_availability) >= IN_ROOM_QTY;
> ELSE
> SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE AND ra.room_id IN (SELECT DISTINCT
> room_avail.room_id FROM cms.sp_get_room_people_capacity(IN_SUPPLIER_ID,
> IN_ROOM_QTY, IN_PEOPLE_QTY, in_culture) room_avail) GROUP BY ra.day HAVING
> sum(ra.room_real_availability) >= IN_ROOM_QTY;
> END IF;
> END IF;
>
> IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
>
> SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
> FROM cms.room_availability ra
> INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
> WHERE
> q.prestadores_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKOUT AND
> ra.allow_check_out IS TRUE AND
> ra.room_id IN (SELECT explode_array(AVAIL) as data);
>
> ELSE
>
> IF IN_PEOPLE_QTY IS NULL THEN
>
> SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
> FROM cms.room_availability ra
> INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
> WHERE
> q.prestadores_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKOUT AND
> ra.allow_check_out IS TRUE AND
> ra.room_id IN (SELECT explode_array(AVAIL) as data)
> GROUP BY ra.day;
>
> ELSE
>
> SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
> FROM cms.room_availability ra
> INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
> WHERE
> q.prestadores_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKO
Re: [SQL] returning the number of rows output by a copy command from a function
2013/1/16 James Sharrett :
> I have a function that generates a table of records and then a SQL statement
> that does a COPY into a text file. I want to return the number of records
> output into the text file from my function. The number of rows in the table
> is not necessarily the number of rows in the file due to summarization of
> data in the table on the way out. Here is a very shortened version of what
> I'm doing:
>
>
> CREATE OR REPLACE FUNCTION export_data(list of parameters)
> RETURNS integer AS
> $BODY$
>
> declare
> My variables
>
> Begin
>
> { A lot of SQL to build and populate the table of records to export}
>
>
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
> HEADER;';
> Execute strSQL;
>
> Return 0;
>
> end
> $BODY$
> LANGUAGE plpgsql VOLATILE
>
> strSQL gets dynamically generated so it's not a static statement.
>
> This all works exactly as I want. But when I try to get the row count back
> out I cannot get it. I've tried the following:
>
> 1.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
> HEADER;';
> Execute strSQL into export_count;
>
> Return export_count;
>
> This give me an error saying that I've tried to use the INTO statement with
> a command that doesn't return data.
>
>
> 2.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
> HEADER;';
> Execute strSQL;
>
> Get diagnostics export_count = row_count;
>
> This always returns zero.
>
> 3.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
> HEADER;';
> Execute strSQL;
>
> Return row_count;
>
> This returns a null.
>
> Any way to do this?
>
not yet
it is fixed in 9.3
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7ae1815961c635fd1a6fe72acb89fdef741a45a8
Regards
Pavel Stehule
>
> Thanks in advance,
> James
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning values from dynamic SQL to a variable
Hello you can use RETURN QUERY EXECUTE statement http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING Regards Pavel Stehule 2013/1/15 kgeographer : > I have a related problem and tried the PERFORM...EXECUTE pattern suggested > but no matter where I put PERFORM I get 'function not found' errors. > > I want to loop through id values returned by a query and execute another > with each i as a parameter. Each subquery will return 6-8 rows. This is a > simplified example, in the real app the subquery is doing some aggregation > work. > > Tried many many things including this pattern below and read everything I > could find, but no go. Any help appreciated. > > > create or replace function getRowsA() returns setof record as $$ > declare > r record; > loopy record; > i integer; > sql text; > begin > for r in select * from cities loop > i := r.id; > sql := 'select city,topic,weight from v_doctopic where city = ' || i; > EXECUTE sql; > return next loopy; > end loop; > return; > end; > $$ language 'plpgsql'; > > select * from getRowsA() AS foo(city int, topic int, weight numeric) > > > > - > karlg > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
2012/11/27 Thomas Kellerer :
> Pavel Stehule, 27.11.2012 13:26:
>
>>> My question is: why I cannot use regexp_matches() in the WHERE clause,
>>> even
>>> when the result is clearly an integer value?
>>>
>>
>> use a ~ operator instead
>>
>
> So that means, regexp_matches cannot be used as an expression in the WHERE
should not be used - it is designed to return matched values, no for
returning true or false,
you can do some obscure
postgres=# select * from o where array(select
(regexp_matches(a,'ne'))[1]) <> '{}'::text[];
a
zdenek
(1 row)
but it is not recommended.
Regards
Pavel
> clause?
>
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
Hello 2012/11/27 Thomas Kellerer : >> > So I tried: >> > >> > SELECT * >> > FROM some_table >> > WHERE regexp_matches(somecol, 'foobar') is not null; >> > >> > However that resulted in: ERROR: argument of WHERE must not return a >> set >> > >> > Hmm, even though an array is not a set I can partly see what the >> problem is >> > (although given the really cool array implementation in PostgreSQL I >> was a bit surprised). >> > >> > >> > So I though, if I convert this to an integer, it should work: >> > >> > SELECT * >> > FROM some_table >> > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0 >> > >> > but that still results in the same error. >> > >> > But array_length() clearly returns an integer, so why does it still >> throw this error? >> > >> > >> > I'm using 9.2.1 >> > > > >> Sounds to me like this: >> >> >> http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html >> > > Thanks, but my question is not related to the underlying problem. > > My question is: why I cannot use regexp_matches() in the WHERE clause, even > when the result is clearly an integer value? > use a ~ operator instead postgres=# select * from o where a ~ 'e'; a pavel zdenek (2 rows) postgres=# select * from o where a ~ 'k$'; a zdenek (1 row) you can use regexp_matches, but it is not effective probably postgres=# select * from o where exists (select * from regexp_matches(o.a,'ne')); a zdenek (1 row) Regards Pavel Stehule > > Regards > Thomas > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] matching a timestamp field
Hello 2012/9/20 BACHELART PIERRE (CIS/SCC) : > Hello, > > > > > > Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? > > Is there something I have missed in the doc ? > you cannot use ~ operator for timestamp, it is nonsense - use '=' instead see 8.3 release notes http://www.postgresql.org/docs/9.1/static/release-8-3.html A dump/restore using pg_dump is required for those wishing to migrate data from any previous release. Observe the following incompatibilities: E.51.2.1. General Non-character data types are no longer automatically cast to TEXT (Peter, Tom) Regards Pavel Stehule > > > > > Welcome to psql 8.1.19, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > >\h for help with SQL commands > >\? for help with psql commands > >\g or terminate with semicolon to execute query > >\q to quit > > > > ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; > >host | exchange | rit | board | var | lceid | pceid | mnem | > eq | rtyp | rv | cetype | record| type | zone > > --+--+-+--+--+---+---+---++--++--+-+--+-- > > and5032t | and5032t | 01a0301 | 21122994 | ebjb | | 000c | con3a | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0307 | 21406298 | aaca | | 000c | mmca | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0309 | 21406298 | aaca | | 000c | mmca | s > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0311 | 21407930 | | | 000c | mmcb | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0313 | 21407932 | abca | | 000c | mcud | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > (5 rows) > > > > ansroc=# \q > > > > > > > > psql (8.4.9) > > Type "help" for help. > > ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; > > ERROR: operator does not exist: timestamp without time zone ~ unknown > > LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; > >^ > > HINT: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > > ansroc=# > > > > > > > > > > Pierre. > > +32 471 68 12 23 > > > > > > > * Disclaimer * > http://www.belgacom.be/maildisclaimer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need to Iterate the record in plpgsql
Hello http://okbob.blogspot.cz/2010/12/iteration-over-record-in-plpgsql.html http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger Regards Pavel Stehule 2012/8/31 Yelai, Ramkumar IN BLR STS : > Hi All, > > I am facing a issue in Iterating the RECORD. > > The problem is, I would like to iterate the RECORD without using sql query, > but as per the syntax I have to use query as shown below. > > FOR target IN query LOOP > statements > END LOOP [ label ]; > > In my procedure, I have stored one of the procedure output as record, which > I am later using in another iteration. Below is the example > > > CREATE OR REPLACE FUNCTION test2() > > Rec1 RECORD; > Rec2 RECORD; > Rec3 RECORD; > > SELECT * INTO REC1 FROM test(); > > FOR REC2 IN ( select * from test3()) > LOOP > FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql > LOOP > > END LOOP > END LOOP > > As per the example, How can I iterate pre stored record. > > Please let me know if you have any suggestions. > > Thanks & Regards, > Ramkumar > > > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
> > This was more like what I was thinking, but I still get an error, which I > don't understand. I have extracted the inner sub-select and it does only > return one record per registration. (The extra criteria is just to ignore old > or cancelled tax requests and doesn't affect the query) > > goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, > s_created, ud_id, ud_handover_date from stock s left outer join (select > ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id, > ud_pex_registration) = (select max(ud_id), ud_pex_registration from > used_diary where (ud_tab is null or ud_tab <> 999) and ud_created > > CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on > s.s_regno = udIn.ud_pex_registration; > ERROR: more than one row returned by a subquery used as an expression sure, I am sorry please, WHERE (ud_id, ud_pex_registration) = (SELECT ... replace by WHERE (..) IN (SELECT .. Regards Pavel > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
2012/5/23 Gary Stainburn :
> Hi folks,
>
> I know I've seen posts like this before but Google isn't helping today.
>
> I have two tables, vehicle stock and tax requests. Each vehicle can be taxed
> more than once, but I only want to pull in the most recent tax request - the
> one with the highest ud_id.
>
> I have the following, which obviously returning multiple records which then
> appears that the same vehicle is in stock multiple times. How can I make it
> so we only show each vehicle once, showing the most recent tax request
> details.
>
>
> select * from stock_details s
> left outer join used_diary u on s.s_registration = u.ud_registration;
select distinct on (s.s_registration) *
... order by u.ud_id desc
or
select *
from stock_details s
left join (select * from used_diary where (ud_id,
ud_registration) = (select max(ud_id), ud_registration from used_diary
group by ud_registration)) x
on s.s_registration = x.ud_registration;
Regards
Pavel Stehule
>
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] generic crosstab ?
Hello try to use cursors http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html Regards Pavel Stehule 2012/4/24 Andreas : > Hi, > > is there a generic solution to dump the result of a query as a crosstab, > when I can't know how many columns I will need? > > E.g. I get something like this: > > id, x > 1, a > 1, b > 1, c > 2, l > 2, m > > > and I'd like to see it as: > > id, x1, x2, x3, . xn > 1, a, b, c, null, null > 2, l, m, > > I fear the problem is I dont know n. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql : adding record variable to table
2012/4/19 thomas veymont : > that made it, thank you. > For other readers, here is what I finally did : > > CREATE TABLE mytable (...) > > CREATE FUNCTION (...) RETURNS SETOF mytable AS $$ > DECLARE > r mytable%rowtype > BEGIN > ... > FOR r IN select * from mytable > LOOP > > RETURN next r; > END LOOP; > RETURN; > END; > > I don't know if %rowtype is actually needed. I found this in here : > http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions > %rowtype is not required - in pg (it is syntax from Oracle), but it is good to use it to increase readability. Regards Pavel > thanks again > Tom > > > 2012/4/19 Pavel Stehule : >> 2012/4/19 thomas veymont : >>> hi Pavel, >>> >>> thanks for your answer, >>> >> >>> I don't understand exactly how "y" should be declared, and how it >>> should be returned by the function (as a table, >>> as a "set of record", or maybe as some kind of generic object, I don't >>> know exactly what's possible with pl/psql.). >>> >> >> r must used predeclared type - declared type or table. It doesn't work >> with "record" type. >> >> Any table specifies composite type too: >> >> create table y(a int, b int); >> >> create or replace function foo() >> returns setof y as $$ >> declare r y; >> begin >> for r in select * from y >> loop >> return next r; >> end loop; >> return; >> end; >> >> you can declare composite type via command CREATE TYPE >> >> create type y as (a int, b int) >> >> Regards >> >> Pavel Stehule >> >>> cheers >>> Tom >>> >>> 2012/4/18 Pavel Stehule : >>>> Hello >>>> >>>> please try: >>>> >>>> postgres=# create or replace function foo() >>>> returns void as $$ >>>> declare r x; >>>> begin >>>> for r in select * from x >>>> loop >>>> insert into y values(r.*); >>>> end loop; >>>> end; >>>> $$ language plpgsql; >>>> >>>> Regards >>>> >>>> Pavel >>>> >>>> 2012/4/18 thomas veymont : >>>>> (sorry my previous email was truncated) >>>>> >>>>> hi, >>>>> >>>>> Here is what I want to do : >>>>> >>>>> I want to check each row of a table against some conditions (this >>>>> check needs some >>>>> processing stuff I can easily code with pl/pgsql). >>>>> >>>>> If the row is OK, I want to add it in a "resulting table", >>>>> else I just ignore the current row and go to next one. >>>>> >>>>> My function looks like this : (simplified) >>>>> >>>>> FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...) >>>>> DECLARE >>>>> g RECORD >>>>> BEGIN >>>>> FOR g in SELECT colum1, column2, ... FROM someTable >>>>> LOOP >>>>> -- do some processing on "g", then decide wheter I want to >>>>> select it or not >>>>> IF (g is selected) THEN >>add g to resulting_table<< >>>>> END LOOP >>>>> RETURN resulting_table >>>>> >>>>> How should I write the "add g to resulting table" part ? >>>>> >>>>> thanks, >>>>> Tom >>>>> >>>>> -- >>>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>>>> To make changes to your subscription: >>>>> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql : adding record variable to table
2012/4/19 thomas veymont : > hi Pavel, > > thanks for your answer, > > I don't understand exactly how "y" should be declared, and how it > should be returned by the function (as a table, > as a "set of record", or maybe as some kind of generic object, I don't > know exactly what's possible with pl/psql.). > r must used predeclared type - declared type or table. It doesn't work with "record" type. Any table specifies composite type too: create table y(a int, b int); create or replace function foo() returns setof y as $$ declare r y; begin for r in select * from y loop return next r; end loop; return; end; you can declare composite type via command CREATE TYPE create type y as (a int, b int) Regards Pavel Stehule > cheers > Tom > > 2012/4/18 Pavel Stehule : >> Hello >> >> please try: >> >> postgres=# create or replace function foo() >> returns void as $$ >> declare r x; >> begin >> for r in select * from x >> loop >> insert into y values(r.*); >> end loop; >> end; >> $$ language plpgsql; >> >> Regards >> >> Pavel >> >> 2012/4/18 thomas veymont : >>> (sorry my previous email was truncated) >>> >>> hi, >>> >>> Here is what I want to do : >>> >>> I want to check each row of a table against some conditions (this >>> check needs some >>> processing stuff I can easily code with pl/pgsql). >>> >>> If the row is OK, I want to add it in a "resulting table", >>> else I just ignore the current row and go to next one. >>> >>> My function looks like this : (simplified) >>> >>> FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...) >>> DECLARE >>> g RECORD >>> BEGIN >>> FOR g in SELECT colum1, column2, ... FROM someTable >>> LOOP >>> -- do some processing on "g", then decide wheter I want to >>> select it or not >>> IF (g is selected) THEN >>add g to resulting_table<< >>> END LOOP >>> RETURN resulting_table >>> >>> How should I write the "add g to resulting table" part ? >>> >>> thanks, >>> Tom >>> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql : adding record variable to table
Hello please try: postgres=# create or replace function foo() returns void as $$ declare r x; begin for r in select * from x loop insert into y values(r.*); end loop; end; $$ language plpgsql; Regards Pavel 2012/4/18 thomas veymont : > (sorry my previous email was truncated) > > hi, > > Here is what I want to do : > > I want to check each row of a table against some conditions (this > check needs some > processing stuff I can easily code with pl/pgsql). > > If the row is OK, I want to add it in a "resulting table", > else I just ignore the current row and go to next one. > > My function looks like this : (simplified) > > FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...) > DECLARE > g RECORD > BEGIN > FOR g in SELECT colum1, column2, ... FROM someTable > LOOP > -- do some processing on "g", then decide wheter I want to > select it or not > IF (g is selected) THEN >>add g to resulting_table<< > END LOOP > RETURN resulting_table > > How should I write the "add g to resulting table" part ? > > thanks, > Tom > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ERROR: operator does not exist: integer = integer[]
Hello this is not error, you cannot use predicate IN in this context use =ANY instead postgres=> select 10 = ANY(ARRAY[1,2,3]); ?column? -- f (1 row) postgres=> select 10 = ANY(ARRAY[1,2,3,10]); ?column? -- t (1 row) Regards Pavel Stehule 2012/4/11 cesar_cast : > I have the following > > select * > from employee AS e > where (e.id) IN (ARRAY[3,1]); > > I have the following mistake > ERROR: operator does not exist: integer = integer[] > LINE 3: where (e.id) IN (ARRAY[3,1]); > ^ > HINT: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > > ** Error ** > > ERROR: operator does not exist: integer = integer[] > SQL state: 42883 > Hint: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > Character: 45 > > > Can anybody explain me? > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/ERROR-operator-does-not-exist-integer-integer-tp5634039p5634039.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to html-decode a html-encoded field
Hello see http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code Regards Pavel Stehule 2012/4/10 JORGE MALDONADO : > I have a table with a varchar field, such a field is HTML ENCODED. So, for > example, the string "PLAIN WHITE T'S" is saved as "PLAIN WHITE T''S" > (double quotes are not part of the string, I use them for clarity only). I > need to perform a SELECT statement on this table and get the values HTML > DECODED and I wonder if there is a function that I can include in such a > statement for this purpose, for example "SELECT htmldecode(fld1) FROM > table1". I will appreciate anu comments about my issue. > > Respectfully, > Jorge Maldonado -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] syntax of joins
2012/4/6 Rob Sargent : > On 04/06/2012 01:23 PM, Pavel Stehule wrote: >> >> Hello >> >> 2012/4/6 Andreas: >>> >>> hi, >>> >>> is there a disadvantage to write a join as >>> >>> select * >>> from a, b >>> where a.id = b.a_id; >>> >> >>> over >>> >>> select * >>> from a join b on a.id = b.a_id; >>> >> >> yes - newer notation has some advantages >> >> * clean specification join predicate and filter predicate >> * simple adaptability to outer join >> * increased protection against copy/paste bug that introduce Cartesian >> product >> >> Regards >> >> Pavel Stehule >> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > In other words, no disadvantage :) > Hard to say - for man who fixed critical cartesian products :) in queries Pavel > -some cranky old guy who still misses "retrieve" > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] syntax of joins
Hello 2012/4/6 Andreas : > hi, > > is there a disadvantage to write a join as > > select * > from a, b > where a.id = b.a_id; > > over > > select * > from a join b on a.id = b.a_id; > yes - newer notation has some advantages * clean specification join predicate and filter predicate * simple adaptability to outer join * increased protection against copy/paste bug that introduce Cartesian product Regards Pavel Stehule > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to write cursors
Hello use a refcursors http://www.postgresql.org/docs/9.1/static/plpgsql-cursors.html Regards Pavel Stehule 2012/4/4 La Chi : > hi every one > > i have created this simple function which returns a column of table , i have > used simple SELECT statement , i simply want to know how can i achieve the > same task with the help of cursor > > CREATE OR REPLACE FUNCTION foo(id int) > returns table(cust_id int) as > $BODY$ > > BEGIN > > return query Select userid from customer where proname ='shampoo'; > END; > $BODY$ > LANGUAGE plpgsql; > > how can i write a cursor which should return all the custid who had proname > shampoo . i shall be very thankful to you > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Fill array with series
Hello
no, there is nothing similar.
Regards
Pavel
2012/3/22 Lee Hachadoorian :
> Is there a single function that will generate a series and return an array?
> The best I've come up with is to combine array_agg and generate_series:
>
> SELECT array_agg(generate_series) FROM generate_series(5, 23);
>
> array_agg
> -
> {5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}
>
> Thanks,
> --Lee
>
> --
> Lee Hachadoorian
> PhD, Earth & Environmental Sciences (Geography)
> Research Associate, CUNY Center for Urban Research
> http://freecity.commons.gc.cuny.edu/
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab
2012/3/12 John Fabiani :
> select *
> from crosstab('select item_number::text, week_of::date, planned_demand::text
> from holding_table order by 1,2')
> as ct(row_name text, week_of date, planned text)
>
> The above does not work. What am I doing wrong?
what it does?
do you have tablefunc extension?
http://www.postgresql.org/docs/9.1/interactive/tablefunc.html
regards
Pavel Stehule
> Johnf
> On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
>> Hello
>>
>> maybe this article helps
>> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
>>
>> there are more ways
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2012/3/12 John Fabiani :
>> > Hi,
>> > I don't know if it because I'm as sick as dog or I'm just a plain idiot
>> > - most likely a little of both.
>> >
>> > Here is my table
>> >
>> > week_of date,
>> > item_number text,
>> > planned_demand integer.
>> >
>> > I have
>> > week_of item_number planned
>> > 2012-02-12 5 200
>> > 2012-02-19 5 -30
>> > 2012-02-26 5 -16
>> >
>> > I want to see
>> >
>> > item_number 2012-02-12 2012-02-19 2012-02-26
>> > 5 200 -30 -16
>> >
>> > I actually have added fields but that should get me started.
>> >
>> > Thanks for help from an old man with a very bad cold.
>> >
>> > Johnf
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab
Hello maybe this article helps http://stackoverflow.com/questions/3002499/postgresql-crosstab-query there are more ways Regards Pavel Stehule 2012/3/12 John Fabiani : > Hi, > I don't know if it because I'm as sick as dog or I'm just a plain idiot - most > likely a little of both. > > Here is my table > > week_of date, > item_number text, > planned_demand integer. > > I have > week_of item_number planned > 2012-02-12 5 200 > 2012-02-19 5 -30 > 2012-02-26 5 -16 > > I want to see > > item_number 2012-02-12 2012-02-19 2012-02-26 > 5 200 -30 -16 > > I actually have added fields but that should get me started. > > Thanks for help from an old man with a very bad cold. > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date arithmetic with columns
Hello 2012/3/1 Peter Faulks : > Bit more googling and I came up with: > > r.utc + CAST( tz.diffmins || ' ' || 'minutes' AS interval) > > It works, but is it the best way? > r.utc + tz.diffmins * interval '1 minute' regards Pavel Stehule > > On 1/03/2012 6:50 AM, Peter Faulks wrote: >> >> I have two columns in two distinct tables, one is the starting time of >> an event, timestamp without time zone. Data is the utc datetime (for >> sorting across time zones), the other is the number of minutes to add. >> >> I am migrating from Firebird. One of the queries uses the dateadd >> function to build a local starting time thus: >> >> SELECT CAST(DATEADD(tz.diffmins MINUTE TO r.utc) AS TIME) AS ts >> FROM races r JOIN tracks t ON t.trk = r.trk >> JOIN timezones tz on tz.state = t.state >> >> The equivalent postgres would be along the lines of >> >> SELECT r.utc + INTERVAL '480 minutes' >> >> How can I substitute the hard-coded 480 for the tz.diffmins? >> > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to shrink database in postgresql
Hello the most similar tool in pg is "VACUUM FULL" statemet; Regards Pavel Stehule 2012/2/29 Rehan Saleem : > hi , > how can i shrink database in postgresql here is a MS-SQL store procedure > which shrinks the database. how same task can be achieved in postgresql. > > ALTER PROCEDURE [dbo].[sp_CleanUpDB] > AS > declare @db nvarchar(50) > select @db = db_name() > DBCC SHRINKDATABASE (@db, 10) > > thanks > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MS-SQL Store Procedure to Postgresql Function
Hello 2012/2/2 F. BROUARD / SQLpro : > Actullay there is no transaction support in internal PG routines. > So the code you posted is not translatable in PG PL/SQL because it involve a > transaction inside the process. It is not exact in this case - it is error handling - and plpgsql supports it - but you can't to rewrite PL code to PostgreSQL one to one. Regards Pavel CREATE OR REPLACE FUNCTION DeleteUserData(ACDetailsID int[]) RETURNS void AS $$ DECLARE _id int; BEGIN -- postgresql has no table type, use a int array instead FOR _id IN array_lower(ACDetailsID,1) .. array_upper(ACDetailsID,1) LOOP DELETE FROM UserAccountDetails WHERE UserDataAcountId= _id; DELETE FROM UserAC WHERE UserDataAcountId= _id; END; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'some issue %', SQLERRM; END; $$ LANGUAGE plpgsql; or little bit more effective code CREATE OR REPLACE FUNCTION DeleteUserData(ACDetailsID int[]) RETURNS void AS $$ DECLARE _id int; BEGIN -- postgresql has no table type, use a int array instead DELETE FROM UserAccountDetails WHERE UserDataAcountId= ANY(ACDetailsID); DELETE FROM UserAC WHERE UserDataAcountId= ANY(ACDetailsID); EXCEPTION WHEN OTHERS THEN RAISE WARNING 'some issue %', SQLERRM; END; $$ LANGUAGE plpgsql; > > A + > > > Le 30/01/2012 07:42, Rehan Saleem a écrit : >> >> hi , how i can convert this store procedure to PostgreSQL function, >> especially I really dont know how to set type to readonly in PostgreSQL. >> thanks >> >> >> ALTERPROCEDURE [dbo].[sp_DeleteUserData] >> @ACDetailsID dbo.ACdetailsID_type READONLY >> AS >> DECLARE@ID int >> begintry >> begintransaction >> DECLARE c_ACDetailsID CURSOR FOR SELECT id FROM @ACDetailsID >> OPEN c_ACDetailsID >> FETCH NEXT FROM c_ACDetailsID INTO @ID >> WHILE (@@FETCH_STATUS = 0) BEGIN >> delete from UserAccountDetails where UserDataAcountId=@ID >> delete from UserAC where UserDataAcountId=@ID >> FETCH NEXT FROM c_ACDetailsID INTO @ID >> End--end of while loop >> committransaction >> CLOSEc_ACDetailsID >> DEALLOCATEc_ACDetailsID >> endtry >> begincatch >> rollback transaction; >> print error_message( > > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.com * > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to Return Table From Function
Hello 2012/1/22 Rehan Saleem : > hi , i have created this function > > CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg integer > ,center_distance integer) > RETURNS varchar AS $$ > > DECLARE percentage record; > BEGIN > > > > select fname, lname, count(userid) totalcount > ,100.00*count(useriddetails)/totaluser into percentage > from users > where userid= user_id and bloodgroup>=bg and > (centredistance<=center_distance or center_distance=1) > group by fname, lname, user_id; > return percentage; > > > its just a dummy function , but all i want to know that how can i return > fname , lname totalcount and percentage from this function in the form of > table , not the return type varchar. > thanks http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING postgres=# create table t2(a int, b int); CREATE TABLE postgres=# insert into t2 values(10,20),(30,40); INSERT 0 2 postgres=# create or replace function rt2() returns table(a int, b int) postgres-# as $$ postgres$# begin postgres$# return query select t2.a, t2.b from t2; postgres$# return; postgres$# end; postgres$# $$ language plpgsql; CREATE FUNCTION postgres=# select * from rt2(); a │ b ┼ 10 │ 20 30 │ 40 (2 rows) Regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL Function
Hello
2012/1/18 Rehan Saleem
> hi
> i want to create a function in postgresql that take input for columns from
> user for example first_name , last_name, addres. and will put them into
> table , and i also want to use exception if user enters wrong data. will
> some one help me how can i create thats function , because i am new to
> postgresql . i know how to do it in MS-SQL , i also have that code .if you
> dont understand what am trying to say . i can post<http://www.dbforums.com/#>
> sql
> code .thanks
>
>
CREATE TABLE users(id serial PRIMARY KEY, first_name varchar(10), last_name
varchar(10));
CREATE OR REPLACE FUNCTION new_user(fname varchar, lname varchar)
RETURNS int AS $$
DECLARE r int;
BEGIN
-- custom exception -- lname cannot be empty or NEMO
IF trim(lname) = '' OR lower(lname) = 'nemo' THEN
RAISE EXCEPTION 'bad last_name: "%"', lname;
END IF;
INSERT INTO users(first_name, last_name) VALUES(lname, fname) RETURNING
id INTO r;
RETURN r;
END;
$$ LANGUAGE plpgsql;
postgres=# select new_user('pavel','stehule');
new_user
--
1
(1 row)
postgres=# select new_user('pavel','very long text');
ERROR: value too long for type character varying(10)
CONTEXT: SQL statement "INSERT INTO users(first_name, last_name)
VALUES(lname, fname) RETURNING id"
PL/pgSQL function "new_user" line 8 at SQL statement
postgres=# select new_user('pavel','nemo');
ERROR: bad last_name: "nemo"
postgres=#
Regards
Pavel Stehule
Re: [SQL] Call function with dynamic schema name
2012/1/15 IlGenna : > Can you provide me e little example plz? CREATE SCHEMA s1; CREATE SCHEMA s2; CREATE TABLE s1.a1(a int); CREATE TABLE s2.a1(a int); CREATE OR REPLACE FUNCTION s1.fx1() RETURNS int AS $$ BEGIN RETURN (SELECT MAX(a) FROM s1.a1); END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION s2.fx1() RETURNS int AS $$ BEGIN RETURN (SELECT MAX(a) FROM s2.a1); END $$ LANGUAGE plpgsql; SET search_path TO s1; SELECT fx1(); -- returns max from s1.a1; SET search_path TO s2; SELECT fx1(); -- returns max from s2.s1; Regards Pavel Stehule > > Thanks in advance. > > Alessio > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146739.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call function with dynamic schema name
Hello 2012/1/15 IlGenna : > Hi to everyone, > I would like to use in my function (plpgsql or sql) dynamic schema name to > execute query or to call other functions. > > For exemple in oracle is possible to excute query in this manner: > > > SELECT * FROM &&SCHEMA_NAME..TABLE_NAME; > > Where I think &&SCHEMA_NAME. is a sessione variable. > > I found tath I can use dynamic SQL like this: > > execute 'select * from ' || schema_name || '.table_name'; > > > However, I would like to know if exist any other system to use dynamic > schema name more similiar to Oracle. Another pl language is also ok. There are no similar way to Oracle. You can set a search_path variable, but you have to be careful, because cached plans in PL/pgSQL can do some issues, when function is called again with different search path. Regards Pavel Stehule > > > Thank you very much. > > > Alessio > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared statements
Hello 2011/12/8 Vad N : > > Hi. > > How can i pass a set of values to prepared statement? > > example > I have a prepared query: > select * from users in ( $1 ) > > i would like to pass: 1,2,3,4 and get: > > select * from users in ( 1,2,3,4 ) > > Any ideas? use a array parameter, please regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does anyone know of any issues around ARRAY UNNEST
Hello This is not known bug - there should be bug in PostgreSQL or your database (data files) can be broken. 2011/11/24 Belinda Cussen > Hi there, > We're having segmentation faults on our postgres 9.1.1 db. It seems to > happen when we use ARRAY unnesting eg: > > UPDATE activity SET activity_recency_timestamp = CURRENT_TIMESTAMP WHERE > venue_id IN (SELECT venue_id FROM UNNEST(v_venue_id_list) venue_id); > > This is not effective code try to use UPDATE activity SET activity_recency_timestamp = CURRENT_TIMESTAMP WHERE venue_id = ANY(v_venue_id_list) Regards Pavel Stehule p.s. It working on my comp postgres=# CREATE TABLE foo (id int primary key, t timestamp, l int[]); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE postgres=# INSERT INTO foo(id, l) SELECT i, ARRAY(SELECT * FROM generate_series(i-10, i)) FROM generate_series(1,1000) g(i); INSERT 0 1000 postgres=# UPDATE foo SET t = CURRENT_TIMESTAMP WHERE id = ANY(l); UPDATE 1000 postgres=# UPDATE foo SET t = CURRENT_TIMESTAMP WHERE id IN (SELECT x FROM unnest(l) x); UPDATE 1000 Regards Pavel Stehule > We are working on a getting a core dump but I was just wondering if there > are any known issues around this construct - especially the aliasing? > Alternatively could there be an issue trying to write or access tmp files? > > > FYI: > v_venue_id_list is an array passed in to the procedure containing 100,000 > INTEGER elements > ? IS THIS TOO MANY ELEMENTS TO PASS? > > table activity has around 3,000,000 rows > CREATE TABLE activity > ( > activity_id serial NOT NULL, > activity_type_key integer NOT NULL, > media_type_key integer NOT NULL, > activity_source_key integer NOT NULL, > venue_id integer NOT NULL, > poster_id integer NOT NULL, > event_id integer, > activity_source_id_value text NOT NULL, > uri text, > media_uri text, > activity_comment text, > posted_dttm timestamp with time zone, > photo_format_code character varying(10), > video_format_code character varying(10), > public_yn character varying(1), > content_reported_yn character varying(1), > last_scored_tstamp timestamp with time zone, > record_expiry_tstamp timestamp with time zone, > record_created_tstamp timestamp with time zone DEFAULT now(), > record_last_updated_tstamp timestamp with time zone DEFAULT now(), > initial_broadcast_to_text text, > image_id integer, > large_media_uri text, > CONSTRAINT activity_pkey PRIMARY KEY (activity_id ), > CONSTRAINT activity_activity_source_key_activity_source_id_value_key > UNIQUE (activity_source_key , activity_source_id_value ) > ); > > > CREATE INDEX activity_poster_ie > ON activity (poster_id ); > > > CREATE INDEX activity_venue_ie > ON activity (venue_id ); > > > -- > [image: Servian Logo] *Belinda Cussen* | Servian Pty > Ltd<http://www.servian.com.au/> | > *m:* 0466 309 169 | *t:* 02 9376 0700 | f*:* 02 9376 0730 >
Re: [SQL] Returning data from multiple functions
2011/11/10 tlund79 : > I know got this far thanks to Pavle Stehule. The function worked and returned > the data when the variables was predefined after "return query". > > When tried to replace these with variables passed through the function call > I got this message; > ERROR: syntax error at or near "RETURN" > LINE 1: ...ll select ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN > ^ > QUERY: select ppr_pf_inn_verdi( $1 , $2 , $3 , $4 , $5 ) union all select > ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN > CONTEXT: SQL statement in PL/PgSQL function "ppr_test_to_funk" near line 7 > > > > CREATE OR REPLACE FUNCTION ppr_test_to_funk(aarstall int, frauke int, tiluke > int, prosjektkode int[], teamkode int[]) > RETURNS setof integer AS > $BODY$ > > I called the function with this: select * from > ppr_test_to_funk(2011,1,52,array[3], array[7,4]) > > Am I lost or are this possible? > > *Updated function:* > > BEGIN > return query > select ppr_pf_inn_verdi($1,$2,$3,$4,$5) > union all > select ppr_pf_inn_antall($1,$2,$3,$4,$5) >>> MISSING SEMICOLON HERE!!! > > RETURN; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > ALTER FUNCTION ppr_test_to_funk() > OWNER TO oystein; > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980786.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different order by behaviour depending on where clause?
Hello 2011/10/28 Jan Bakuwel : > Hi, > > I have a compound query with some grouping, having and order by's saved > as a view, say with name "myview". > > A) select * from "myview" returns the results as expected in the correct > order (the order by is on fields "Category", "Year", "Month" and a few > other fields). The results are correctly ordered by these fields as > specified in the view. > > B) select * from "myview" where "Year"=2011 and "Month"=1 also returns > the results as expected in the correct order (a subset of A). > > however > > C) select * from "myview" where "Year"=2011 and "Month" >= 1 and "Month" > <= 1 returns the same resultset as B but the order of the rows is not > correct (not even close; haven't been able to see any pattern). > > Any idea how I can further analyse/diagnose this? > > regards, > Jan > Look on EXPLAIN - these queries will have a different execution plan http://www.postgresql.org/docs/8.4/static/sql-explain.html Regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] advice on how to store variable attributes
> > Yeah, thanks for the advice David, if i understand you. this is (much better > explained) my option 3, i have used this format any times for configuration > tables with great success. > > I am not speaking about much data, maybe 200~300 invoices every month so this > should not be a problem in a long long time, the problem i see with this way > of > doing things it is how should i in the report queries get this values as > columns > to compare change in time? something like this? > > SELECT inv.invoice_id, > inv.total, > (SELECT amount_value > FROM vendor_invoices_attrs > WHERE invoice_id = inv.invoice_id > AND amount_category = 'international call minutes'), > (SELECT amount_value > FROM vendor_invoices_attrs > WHERE invoice_id = inv.invoice_id > AND amount_category = 'national call minutes') > FROM vendor_invoices AS inv This should be rewritten little bit more readable SELECT inv.invoice_id, inv.total, a1.amount_value, a2.amount_value FROM vendor_invoices inv, vendor_invoices_attrs a1, vendor_invoices_attrs a2 WHERE a1.id = inv.id AND a2.id = inv.id AND a1.amount_category = 'international call minutes' AND a2. amount_category = 'national call minutes' But it feature of EAV and similar models - you has a less database objects and more complex queries. > > With hstore i think that could be: > SELECT inv.invoice_id, > inv.total, > inv.store -> 'international call minutes', > inv.store -> 'national call minutes' > FROM vendor_invoices > > For me the last option seems preferable but maybe i am missing a better way to > get the first result or an added problem of hstore (other than datatypes > stored > that i think i could check for validity in application or database regardless > of > not been integrated in hstore). > > Regards, > Miguel Angel. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] advice on how to store variable attributes
2011/10/22 David Johnston : > On Oct 22, 2011, at 10:07, Pavel Stehule wrote: >> 2011/10/22 David Johnston : >>> On Oct 22, 2011, at 6:41, Linos wrote: >>> >>>> Hi all, >>>> i need a little of advice on what could be the best way to store this >>>> information. >>>> >>>> We need to calculate the difference in costs for our operations, we are >>>> already >>>> storing our vendor invoices in the database so calculate the monetary >>>> change it >>>> is a no-brainer but we need to store special attributes for any of the >>>> invoices >>>> that we need to compare too, for example: >>>> -electric provider: total Kw. >>>> -water provider: total m3. >>>> -car maintenance: kilometers of the car. >>>> -mobile phones provider: international call minutes, national minutes, >>>> number >>>> of sms, etc.. >>>> >>>> And much more types/variables, the number of variables can change, not >>>> every day >>>> but still can change, i would like that they can be defined/changed from >>>> our >>>> application, so alter table to add columns don't seem the best way (still >>>> an >>>> option though). We will have "generic" reports that will show us changes in >>>> costs and specific reports for the types with "extended attributes" that >>>> we want >>>> to compare. >>>> >>>> To compare values from this "extended attributes" i think we have two ways: >>>> 1- have them in columns and use standard SQL. >>>> 2- create the columns with a function that reads this attrs and create >>>> the columns. >>>> >>>> So far i thin we have this options: >>>> 1- a bunch of columns that would be null except when the type of the >>>> invoice >>>> uses them. >>>> 2- a table related with the vendor invoices table for every type of >>>> invoice >>>> with his specifics columns. >>>> 3- a key/value in a separate table related with the vendor invoices >>>> table where >>>> i store the extended attrs of every invoice that needs them. >>>> 4- use a hstore column in the vendor invoces table to store this attrs. >>>> >>>> The first two have the problem of probably changes to the number of >>>> attributes >>>> of every type and give a more closed solution, apart from that 1- seems to >>>> be a >>>> bit awkward and 2- would need the application that creates the query to >>>> know >>>> with what table should join for every type (other point we will need to >>>> change >>>> if we want to create new invoices types). >>>> >>>> The last two have his own problems too, with 3 i will need to create a >>>> function >>>> that return rows as columns to compare them, with 4- given that i will >>>> store the >>>> attrs of every type in the database anyway i can use the operator -> (with >>>> a >>>> CASE using operator ? returning 0 if the searched attr it is not in the >>>> hstore) >>>> but still don't seem a clean solution for me. >>>> >>>> For me it seems i am missing something, probably any of you have a much >>>> more >>>> elegant (or correct) way to handle this situation, what would be your >>>> advice? >>>> Thanks. >>>> >>>> >>> >>> Create a table with a single numeric column and multiple category columns. >>> >>> ( amount_value, amount_unit, amount_category, vendor_id ) >> >> >> This is EAV model - is good for smaller datasets, for larger datasets >> is problematic. There is second possibility - using a "hstore" contrib >> module - that emulates HASH table - It has better for larger datasets. >> >> Regards >> >> Pavel Stehule > > Store was mentioned by the OP. > > Any suggestions on where the line between small and large is drawn? > > Partitions could help in the larger cases. > > My personal first choice is to use separate tables. If going EAV route plan > on eventually moving to the separate table route and at least try to make > migration relatively easy. > > Since both models capture the same data the decision at least partially rests &
Re: [SQL] advice on how to store variable attributes
2011/10/22 David Johnston : > On Oct 22, 2011, at 6:41, Linos wrote: > >> Hi all, >> i need a little of advice on what could be the best way to store this >> information. >> >> We need to calculate the difference in costs for our operations, we are >> already >> storing our vendor invoices in the database so calculate the monetary change >> it >> is a no-brainer but we need to store special attributes for any of the >> invoices >> that we need to compare too, for example: >> -electric provider: total Kw. >> -water provider: total m3. >> -car maintenance: kilometers of the car. >> -mobile phones provider: international call minutes, national minutes, >> number >> of sms, etc.. >> >> And much more types/variables, the number of variables can change, not every >> day >> but still can change, i would like that they can be defined/changed from our >> application, so alter table to add columns don't seem the best way (still an >> option though). We will have "generic" reports that will show us changes in >> costs and specific reports for the types with "extended attributes" that we >> want >> to compare. >> >> To compare values from this "extended attributes" i think we have two ways: >> 1- have them in columns and use standard SQL. >> 2- create the columns with a function that reads this attrs and create >> the columns. >> >> So far i thin we have this options: >> 1- a bunch of columns that would be null except when the type of the >> invoice >> uses them. >> 2- a table related with the vendor invoices table for every type of >> invoice >> with his specifics columns. >> 3- a key/value in a separate table related with the vendor invoices table >> where >> i store the extended attrs of every invoice that needs them. >> 4- use a hstore column in the vendor invoces table to store this attrs. >> >> The first two have the problem of probably changes to the number of >> attributes >> of every type and give a more closed solution, apart from that 1- seems to >> be a >> bit awkward and 2- would need the application that creates the query to know >> with what table should join for every type (other point we will need to >> change >> if we want to create new invoices types). >> >> The last two have his own problems too, with 3 i will need to create a >> function >> that return rows as columns to compare them, with 4- given that i will store >> the >> attrs of every type in the database anyway i can use the operator -> (with a >> CASE using operator ? returning 0 if the searched attr it is not in the >> hstore) >> but still don't seem a clean solution for me. >> >> For me it seems i am missing something, probably any of you have a much more >> elegant (or correct) way to handle this situation, what would be your advice? >> Thanks. >> >> > > Create a table with a single numeric column and multiple category columns. > > ( amount_value, amount_unit, amount_category, vendor_id ) This is EAV model - is good for smaller datasets, for larger datasets is problematic. There is second possibility - using a "hstore" contrib module - that emulates HASH table - It has better for larger datasets. Regards Pavel Stehule > > If necessary each "amount_value" data type should have it's own table since > the processing logic will vary (I.e., you cannot subtract text or Boolean > values). > > You are , in effect, creating multiple tables but combining them into one and > using the category column to distinguish between them. > > David J. > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011/8/31 Emi Lu :
> On 08/31/2011 03:16 AM, Emre Hasegeli wrote:
>>
>> 2011/8/30 Emi Lu:
>>
>>> First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will
>>> work for me.
>>>
>>> But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?
>>
>> It is not. It is like "where id = (3, 5, 7)".
>
>
> What I mean is ilike ('%str1%', ... '%strN%')
>
> I just forgot to put %
it useless to introduce non SQL feature where some native feature exists now.
Regards
Pavel Stehule
>
> Emi
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Confused about writing this stored procedure/method.
Hello 2011/8/22 JavaNoobie : > Hi All, > I'm trying to write a stored procedure /function to re-order a set of > calendar months.I have a set of calendar months stored from January to > December in my tables. And as of now when I do order by on this column , > the data is ordered alphabetically , starting April, august etc. and so on > I want to order these months starting from April through March in order to > sync with the financial calendar . I'm trying to write a stored procedure to > do the same (I'm not aware of any other method that Postgres offers this > reordering , if there's any , please do let me know!). > I intend pass the number of the month(say 1 for January , 2 for February > etc>) as the parameter to this method and return a number that corresponds > to the index of the month in the new order , say I pass 8 for August , I > return 11 , in order to get the index of August in he financial year > calendar somewhat like this > > CREATE FUNCTION getNMonth(to_number(domain.description,'MM') int as num) this is wrong - you can use only a variable name and type name in parameter's list > RETURNS int AS $$ > DECLARE > qty int; > BEGIN > SELECT qty, > CASE WHEN num=4 THEN 1-- Set August to the first month etc. > WHEN num=5 THEN 2 > ELSE 'other' > END > FROM DOMAIN; probably you would to use a SELECT INTO ... > RETURN qty; > END; > $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getNMonth(num int) RETURNS int AS $$ DECLARE qty int; BEGIN SELECT CASE num WHEN 4 THEN 1 WHEN 5 THEN 2 .. INTO qty; RETURN qty; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; try to read a documentation first, please http://www.postgresql.org/docs/9.0/interactive/plpgsql.html Regards Pavel Stehule > However, this throws a syntax error on to_number. This my first attempt at a > stored procedure in Postgres .Thank you for your time. > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Confused-about-writing-this-stored-procedure-method-tp4723656p4723656.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorting months according to fiscal year
Hello
2011/8/22 Enzen user :
> Hi
> I have to rearrange the months according to the fiscal year i.e from April
> to march and use the same in the order by clause of a query.
> I have written the following postgresql function for the same, but to_number
> is returning an error.
> Can you please tell me where i'm going wrong?
> Instead of the function to_number can you suggest any other function that
> will convert a particular month to its corresponding month number(ex:
> april=4 or jan=1)
>
>
> CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$
you cannot use to_number function inside param's list
> DECLARE
>
> BEGIN
>
> CASE WHEN 4 THEN 1
> WHEN 5 THEN 2
> WHEN 6 THEN 3
> WHEN 7 THEN 4
> WHEN 8 THEN 5
> WHEN 9 THEN 6
> WHEN 10 THEN 7
> WHEN 11 THEN 8
> WHEN 12 THEN 9
> WHEN 1 THEN 10
> WHEN 2 THEN 11
> WHEN 3 THEN 12
> ELSE 0
> END;
>
>
>
> $$ LANGUAGE plpgsql;
>
CREATE OR REPLACE FUNCTION sort_month(int)
RETURNS int -- is wrong to use numeric here
AS $$
...
$$ LANGUAGE sql;
SELECT sort_ month(to_nuber(...))
Regards
Pavel Stehule
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cursor names in a self-nested function
Hello you can use a refcursor type http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html Regards Pavel Stehule 2011/8/18 Kidd, David M : > Hi, > > I am trying to write a function that contains a cursor and iteratively calls > itself. > > It is along the lines of, > > CREATE FUNCTON test(id integer) RETURNS TEXT AS > $BODY$ > DECLARE > mycursor CURSOR FOR SELECT * FROM myfunction(id); > newid INTEGER; > out = TEXT; > BEGIN > out := ''; > OPEN mycursor; > LOOP > FETCH my_cursor INTO newid; > out := out || test (newid); > END LOOP; > RETURN out; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > > This returns an ERROR stating that "mycursor" is already in use. > > I understand this occurs because cursor names must be unique across, as well > as within, functions. > > So, my question is whether there is a way I can dynamically declare a cursor > name, for example by appending a incremental number or guid to make the name > unique? > Just trying to concatenate two passed arguments in the DECLARE statement > unsurprisingly fails. > > Any other solutions are of cause welcome. > > Many thanks, > > - David > > > > David M. Kidd > > Research Associate > Center for Population Biology > Silwood Park Campus > Imperial College London > 0207 594 2470 > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] F_TABLE_NUMBER_RANGE equivalent in Postgresql
Hello this is not built in MSSQL, but PostgreSQL has a "generate_series" function Regards Pavel Stehule 2011/8/16 msi77 : > > Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server? > I suppose that this is UDF written by user. > > > 16.08.2011, 08:53, "Yuan HOng" : >> Hi, >> >> With MS-SQL sever, there is a built-in utility function >> dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the >> given range. For example: >> >> select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return: >> >> number >> --- >> 1 >> 2 >> 3 >> 4 >> 5 >> >> This is extremely convenient for generating consecutive dates. For >> example, I want to find out the daily sales information and if on some >> day there is no sales, the query should return 0 instead of a missing >> date. >> >> In this case it is necessary to first create a list of consecutive >> dates and then left join to some fact table. >> >> With the range function I can simply write something like >> >> select start_date + interval number >> from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days) >> >> How does one accomplish such task with Postgresql? >> >> -- >> Hong Yuan >> >> 大管家网上建材超市 >> 装修装潢建材一站式购物 >> http://www.homemaster.cn >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LTREE extension and "order by"
2011/7/21 Carla : > Hmm, I'm using PostgreSQL 8.4 and it worked. > Try to use the function ltree2text instead of ::text. > select * from comments where article_id = 2 order by > cast(string_to_array(ltree2text(path),'.') as integer[]); > this does not work in older versions you can use http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Cast_to_varchar Regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] newbie question * compare integer in a "where IN" statement
Hello
you can try
SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',','))
other forms are slow
Regards
Pavel Stehule
2011/7/13 Jose Ig Mendez
>
> Hi everybody,
>
> I'm trying to compare in a sentence like this (using PostGres 8.3) :
>
> select * from myTable where id_integer IN ('1,2,3,4')
>
> I want to get the records which key "id_integer" is 1 or 2 or 3 or 4. the
> type od my "id", of course, is integer.
>
> I've tried many differents ways but I cannot get the result I want.
> I would like to cast the integer parameter I cannot change the part after
> "IN" it has to be a "string list".
>
> Do I have to use a function ? I would like not to use it.
>
> How can I compare a key (integer) with a lists of values ?
>
> I'm desperate
>
> THANX IN ADVANCE
>
> --
> --
>
> José Ignacio Méndez Yanes
>
> Área de Operaciones / Area of Operations
>
> Phone: +34 916 011 373 / +34 946 416 066
>
> Mobile: +34 666 431 099
>
> Fax: +34 916 011 372 / +34 944 318 286
> [image: Ándago] Alcalde Ángel Arroyo 10, 1ª Planta. 28904. Getafe,
> Madrid
> Kanala Bidea, Edif. 103, 1ª Izda. Parque Tecnológico. 48170. Zamudio,
> Bizkaia
> www.andago.com
>
> Síguenos en: Twitter <http://twitter.com/andago> -
> Facebook<http://www.facebook.com/pages/Andago/111911732048>-
> LinkedIn <http://www.linkedin.com/groups?gid=1479457> -
> YouTube<http://www.youtube.com/andagotv>
> Antes de imprimir este mensaje, asegúrese de que es necesario.
> Consider the environment before printing this mail.
>
> AVISO LEGAL
> --
>
> *ANDAGO CONSULTING SL / ANDAGO INGENIERÍA, SL* le informa que los datos
> facilitados por Ud. y utilizados para el envío de esta comunicación serán
> objeto de tratamiento automatizado o no en nuestros ficheros, con la
> finalidad de gestionar la agenda de contactos de nuestra empresa y para el
> envío de comunicaciones profesionales por cualquier medio electrónico o no.
> Vd. podrá en cualquier momento ejercer el derecho de acceso, rectificación,
> cancelación y oposición en los términos establecidos en la Ley Orgánica
> 15/1999. El responsable del tratamiento es ANDAGO CONSULTING SL / ANDAGO
> INGENIERIA SL, con domicilio en C/ ALCALDE ANGEL ARROYO, 10, 28904, Getafe
> (Madrid).
>
> El contenido de esta comunicación, así como el de toda la documentación
> anexa, es confidencial y va dirigido únicamente al destinatario del mismo.
> En el supuesto de que usted no fuera el destinatario, le solicitamos que nos
> lo indique y no comunique su contenido a terceros, procediendo a su
> destrucción.
>
> DISCLAIMER
> --
>
> The content of this communication and any attached information is
> confidential and exclusively for the use of the addressee. If you are not
> the addressee, we ask you to notify to the sender and do not pass its
> content to another person, and please be sure you destroy it.
>
Re: [SQL] using explain output within pgsql
2011/7/10 Uwe Bartels : > Hi Pavel, > > is it posible to get this running even with dynamic sql? > I didn't write that. I'm using execute to run this create table > probably yes postgres=# do $$ declare x text; begin execute e'explain(format yaml) select * from data where value = \'a\'' into x; raise notice '%', x; end; $$ language plpgsql; NOTICE: - Plan: Node Type: "Seq Scan" Relation Name: "data" Alias: "data" Startup Cost: 0.00 Total Cost: 23.38 Plan Rows: 5 Plan Width: 46 Filter: "((value)::text = 'a'::text)" DO > best regards, > Uwe > > On 10 July 2011 21:20, Pavel Stehule wrote: >> >> Hello >> >> >> >> 2011/7/10 Uwe Bartels : >> > Hi, >> > >> > I'm starting up a datawarehouse with patitioning. >> > my etl processes write directly into the corresponding partitions >> > instead of >> > using triggers. >> > >> > The reports I run in the datawarehouse are stored in a cache within the >> > same >> > database. >> > Now I'd like to store besides the results the dependencies to the tables >> > which were used to generate the report. with this information i could >> > invalidate cache results for the tables I'm going to import with my etl >> Hello >> >> try >> >> FOR l_explain IN EXPLAIN ANALYZE ... >> LOOP >> ... >> >> Regards >> >> Pavel Stehule >> >> > processes. >> > >> > explain analyze gives me the information which table or patition is read >> > from for each report. e.g >> > explain analyze (FORMAT YAML) create table cache.report234 as select >> > col1,col2,sum(clicks) from dwh.event_log_weekly where week >= >> > '2011-06-27' >> > and week <= '2011-07-11' group by col1,col2; >> > >> > now I'd like to store the output of explain analyze in a pgsql variable >> > for >> > further processing. that looks something like this. >> > >> > DO $$declare l_explain text; >> > begin >> > l_explain := explain analyze (FORMAT YAML) create table cache.report234 >> > as >> > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >= >> > '2011-06-27' and week <= '2011-07-11' group by col1,col2; >> > select l_explain; >> > end$$; >> > >> > But that doesn't work. I get a syntax error. >> > >> > Does anybody has an idea how to retrieve the output of explain within >> > pgsql >> > and store this in a variable? >> > An alternative would be any other way to extract the information about >> > tables used by arbitrary sql statements. >> > >> > best regards, >> > Uwe >> > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using explain output within pgsql
Hello 2011/7/10 Uwe Bartels : > Hi, > > I'm starting up a datawarehouse with patitioning. > my etl processes write directly into the corresponding partitions instead of > using triggers. > > The reports I run in the datawarehouse are stored in a cache within the same > database. > Now I'd like to store besides the results the dependencies to the tables > which were used to generate the report. with this information i could > invalidate cache results for the tables I'm going to import with my etl Hello try FOR l_explain IN EXPLAIN ANALYZE ... LOOP ... Regards Pavel Stehule > processes. > > explain analyze gives me the information which table or patition is read > from for each report. e.g > explain analyze (FORMAT YAML) create table cache.report234 as select > col1,col2,sum(clicks) from dwh.event_log_weekly where week >= '2011-06-27' > and week <= '2011-07-11' group by col1,col2; > > now I'd like to store the output of explain analyze in a pgsql variable for > further processing. that looks something like this. > > DO $$declare l_explain text; > begin > l_explain := explain analyze (FORMAT YAML) create table cache.report234 as > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >= > '2011-06-27' and week <= '2011-07-11' group by col1,col2; > select l_explain; > end$$; > > But that doesn't work. I get a syntax error. > > Does anybody has an idea how to retrieve the output of explain within pgsql > and store this in a variable? > An alternative would be any other way to extract the information about > tables used by arbitrary sql statements. > > best regards, > Uwe > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] overload
Hello
using a "window" implemented via LIMIT OFFSET is not good - it is
solution on some systems where cursors are not available, but it is
bad solution on PostgreSQL. Use a cursor instead - it is significantly
more efective with less memory requests.
Regards
Pavel Stehule
2011/7/8 Viktor Bojović :
> Thanx Wayne,
> at the end i did it that way and it works.
> The code is below.
> CREATE FUNCTION pattern_counter1("patLength" integer) RETURNS character
> varying
> LANGUAGE plperl
> AS $_X$
> my $rvCnt = spi_exec_query("select count(1) as cnt from entry");
> #my $rowCountAll = $rvCnt->{processed};
> my $row = $rvCnt->{rows}[0];
> my $rowCountAll = $row->{cnt};
> my $windowSize = 50;
> my %patterns=();
> for (my $p=0;$p<$rowCountAll;$p+=$windowSize){
> my $sql="select sequence from entry limit $windowSize offset $p";
>
> my $rv = spi_exec_query($sql);
> my $rowCount = $rv->{processed};
> my $patLen = $_[0];
> my $patt = '';
>
> foreach my $rn (0 .. $rowCount -1){
> my $row = $rv->{rows}[$rn];
> my $seq = $row->{sequence};
> for (my $x = 1;$x<=length($seq) - $patLen;$x++){
> $patt=substr($seq,$x,$patLen);
> if (! defined $patterns{$patt}) {
> $patterns{$patt}=1;
> }else{
> $patterns{$patt}++;
> }
> }
> }
> }
>
> foreach $patt (keys %patterns){
> my $sql="insert into patterns values('".$patt."',".$patterns{$patt}.")";
> spi_exec_query($sql);
> }
> return $tmp;
> $_X$;
>
>
> On Fri, Jul 8, 2011 at 8:50 PM, wrote:
>>
>> I'm have the same situation with large tables. Take a look at using a
>> cursor to fetch several thousand rows at a time. I presume what's
>> happening is that perl is attempting to create a massive list/array in
>> memory. If you use a cursor the list should only contain X number of
>> rows where X in the number specified at each fetch execution. You'll
>> need to define the cursor inside a transaction block.
>>
>> - begin transaction
>> - define the cursor
>> - fetch rows from cursor
>> - while row count from previous step > 0, execute previous step
>> - terminate transaction
>>
>> Or you could use plpgsql instead of plperl, FOR loops over result sets in
>> plpgsql implicitly use cursors... it's just a little less code.
>>
>> Hope that helps,
>> Wayne
>>
>> On Tue, Jul 05, 2011 at 10:29:03PM +0200, Viktor Bojovi?? wrote:
>> > Hi,
>> > while reading 20GB table through PL/PERL function , it constantly grows
>> > in
>> > RAM.
>> > I wanted to ask you which is the best way to read table inside that
>> > function without such memory consumption.
>> > Thanks in advance
>> >
>> > Code is here:
>> >
>> > CREATE FUNCTION pattern_counter("patLength" integer)
>> > RETURNS varchar AS
>> > $BODY$
>> > my $rv = spi_exec_query("select sequence from entry");
>> > my $rowCount = $rv->{processed};
>> > my $patLen = $_[0];
>> > my $patt = '';
>> > my %patterns=();
>> > foreach my $rn (0 .. $rowCount -1){
>> > my $row = $rv->{rows}[$rn];
>> > my $seq = $row->{sequence};
>> > for (my $x = 1;$x<=length($seq) - $patLen;$x++){
>> > $patt=substr($seq,$x,$patLen);
>> > if (! defined $patterns{$patt}) {
>> > $patterns{$patt}=1;
>> > }else{
>> > $patterns{$patt}++;
>> > }
>> > }
>> > }
>> > foreach $patt (keys %patterns){
>> > my $sql="insert into patterns
>> > values('".$patt."',".$patterns{$patt}.")";
>> > spi_exec_query($sql);
>> > }
>> > return '';
>> > $BODY$
>> > LANGUAGE plperl VOLATILE
>> > COST 100;
>> >
>> >
>> >
>> > --
>> > ---
>> > Viktor Bojovi??
>> > ---
>> > Wherever I go, Murphy goes with me
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Usage of function retruning record in query
2011/7/5 gmb : > > gmb wrote: >> >> >> Thanks for the feedback, Harald. >> >> How about specifying different aliases to the resulting values? >> This will be handy when I use the same function multiple times in the same >> query. >> (the function will take another input parameters used in the calculations) >> >> E.g.: >> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid, >> '2011-06-07')).* FROM itemlist; >> >> itemid | calcval1 | calcval2 | calcval1 | calcval2 >> +--+--+--+-- >> 4 | 0.67 | 10.00 | 0.64 | 65.23 >> 5 | 1.55 | 45.00 | 1.23 | 23.25 >> 6 | 3.60 | 69.00 | 2.98 | 62.66 >> How will I manage unique column names for this output? >> > Hmm.. no takers? I guess not possible then? > Thanks anyway > hello try to wrap your query to subselect, npcps_201=# select 1,2,2,3; ?column? │ ?column? │ ?column? │ ?column? ──┼──┼──┼── 1 │2 │2 │3 (1 row) Time: 0.171 ms npcps_201=# select * from (select 1,2,2,3) x (a,b,c,d); a │ b │ c │ d ───┼───┼───┼─── 1 │ 2 │ 2 │ 3 (1 row) Time: 0.202 ms Regards Pavel Stehule > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p4552513.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] best performance for simple dml
Hello
2011/6/27 chester c young
> forgive me for brain storming a little re copy:
>
> if there are a limited number of tables you're inserting, would there be
> anything wrong with the app opening a copy connection? ie, a connection
> initiates the copy and then stays open like a pipe for any inserts coming
> through it. visually it's a very cool paradigm, but is it actually a good
> idea?
>
depends on application. Usually you can use a connection better than just
"insert connection". I am thinking, so it doesn't carry some special - it
remove a connection cost, but nothing more. You can use a more connections
to do paralel inserts - it has a sense.
look on pgpool or other similar sw for connection pooling
Pavel
>
> --- On *Mon, 6/27/11, Pavel Stehule * wrote:
>
>
> From: Pavel Stehule
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young"
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 1:05 AM
>
> 2011/6/27 chester c young
> http://mc/compose?to=chestercyo...@yahoo.com>
> >
> >
> > two questions:
> > I thought copy was for multiple rows - is its setup cost effective for
> one row?
>
> I expect it will be faster for one row too - it is not sql statement
>
> if you want to understand to performance issues you have to understand to
>
> a) network communication costs
> b) SQL parsing and SQL planning costs
> c) commits costs
> d) other costs - triggers, referential integrity costs
>
> >
> > copy would also only be good for insert or select, not update - is this
> right?
>
> sure,
>
> If you need to call a lot of simple dml statement in cycle, then
>
> a) try tu move it to stored function
> b) if you can't to move it, then ensure, so statements will be
> executed under outer transaction
>
> slow code
>
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
>
> 10x faster code
>
> exec('begin');
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
> exec('commit');
>
> Regards
>
> Pavel Stehule
>
> >
> > --- On Mon, 6/27/11, Pavel Stehule
> > http://mc/compose?to=pavel.steh...@gmail.com>>
> wrote:
> >
> > From: Pavel Stehule
> > http://mc/compose?to=pavel.steh...@gmail.com>
> >
> > Subject: Re: [SQL] best performance for simple dml
> > To: "chester c young"
> > http://mc/compose?to=chestercyo...@yahoo.com>
> >
> > Cc: pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org>
> > Date: Monday, June 27, 2011, 12:35 AM
> >
> > Hello
> >
> > try it and you will see. Depends on network speed, hw speed. But the most
> fast is using a COPY API
> >
> > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
> >
> > Regards
> >
> > Pavel Stehule
> >
> >
> > 2011/6/27 chester c young
> > http://mc/compose?to=chestercyo...@yahoo.com>
> >
> >
> > what is the best performance / best practices for frequently-used simple
> dml, for example, an insert
> > 1. fast-interface
> > 2. prepared statement calling "insert ..." with binary parameters
> > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
> >
>
> --
> Sent via pgsql-sql mailing list
> (pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
Re: [SQL] best performance for simple dml
2011/6/27 chester c young
>
> two questions:
> I thought copy was for multiple rows - is its setup cost effective for one
> row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to
a) network communication costs
b) SQL parsing and SQL planning costs
c) commits costs
d) other costs - triggers, referential integrity costs
>
> copy would also only be good for insert or select, not update - is this right?
sure,
If you need to call a lot of simple dml statement in cycle, then
a) try tu move it to stored function
b) if you can't to move it, then ensure, so statements will be
executed under outer transaction
slow code
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
10x faster code
exec('begin');
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
exec('commit');
Regards
Pavel Stehule
>
> --- On Mon, 6/27/11, Pavel Stehule wrote:
>
> From: Pavel Stehule
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young"
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. Depends on network speed, hw speed. But the most
> fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 chester c young
>
> what is the best performance / best practices for frequently-used simple dml,
> for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert ..." with binary parameters
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] best performance for simple dml
Hello try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html Regards Pavel Stehule 2011/6/27 chester c young > what is the best performance / best practices for frequently-used simple > dml, for example, an insert > > 1. fast-interface > > 2. prepared statement calling "insert ..." with binary parameters > > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc > takes its arguments and performs an insert using them > >
Re: [SQL] a strange order by behavior
2011/6/23 Peter Eisentraut : > On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote: >> 2011/6/22 Peter Eisentraut : >> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> >> Pavel suggested using a collation of ucs_basic, but I get an error >> >> when I >> >> try that on linux: >> >> >> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test >> >> createdb: database creation failed: ERROR: invalid locale name ucs_basic >> > >> > ucs_basic is a collation name, which is an SQL object. The argument of >> > createdb --lc-collate is an operating system locale name. You can't mix >> > the two, even though they are similar. >> > >> >> ok, what I can to select, when I would to use a C like default order? > > createdb --locale=C --encoding=UTF8 > ok, thank you Pavel > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a strange order by behavior
2011/6/22 Peter Eisentraut : > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> Pavel suggested using a collation of ucs_basic, but I get an error >> when I >> try that on linux: >> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test >> createdb: database creation failed: ERROR: invalid locale name ucs_basic > > ucs_basic is a collation name, which is an SQL object. The argument of > createdb --lc-collate is an operating system locale name. You can't mix > the two, even though they are similar. > ok, what I can to select, when I would to use a C like default order? Regards Pavel > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a strange order by behavior
> I'm actually surprised that european users aren't complaining about this all > the time, but maybe european users are used to seeing things ordered in a > manner which doesn't honour the 'correct' ordering of accented characters. > Actually, I wonder if the probable explanation of the lack of complaint is > the fact that the kinds of fields most apps would tend to do alphabetical > sorts on probably don't tend to have lots of punctuation other than spaces, > so perhaps the language sensitive sorts are deemed sufficient because most > people don't notice the funky behaviour with punctuation and whitespace > while case-insensitive sort is probably desired most of the time. I checked czech UTF8 collation and it is correct postgres=# select * from x order by a collate ucs_basic; a --- Chromečka Crha Semerád Syn Záruba Šebíšek (6 rows) postgres=# select * from x order by a collate "cs_CZ"; a --- Crha Chromečka Semerád Syn Šebíšek Záruba (6 rows) Regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a strange order by behavior
Hello Peter > Pavel suggested using a collation of ucs_basic, but I get an error when I > try that on linux: > $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test > createdb: database creation failed: ERROR: invalid locale name ucs_basic isn't this a bug in collations? Regards Pavel > I was able to create the db with --lc_collate=C and get case-sensitive > sorting that treats spaces 'correctly,' but I have no idea how reliable that > is with multibyte characters and it almost certainly doesn't handle accented -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a strange order by behavior
Hello a equalent of C collate for UTF8 is ucs_basic Regards Pavel Stehule 2011/6/22 Samuel Gendler : > > > On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde wrote: >> >> the database collation is: en_US.UTF-8 >> drop table t1; >> create table t1 (recid int ,f1 varchar(20)); >> insert into t1 values (1,'a'); >> insert into t1 values (2,' '); >> insert into t1 values (3,'aa'); >> insert into t1 values (4,' a'); >> select * from t1 order by f1 >> result: >> recid f1 >> 2 " " >> 1 "a" -- 2 comes before 1 because space is smaller then 'a'. >> fine. >> 4 " a" -- now is see that 1 comes before 4 because space is >> greater then 'a' !? >> 3 "aa" -- now again, 4 comes before 3 because space is smaller >> the 'a' !?! > > I seem to recall a thread here about it ignoring spaces entirely in that > collation (and maybe ignoring capitalization, too?). I went to go test that > assertion by initializing a database with C collation and got some complaint > about it being incompatible with my template1 template database. I > initialized a db off of template0 and then got side tracked and you've only > just reminded me of it. I was planning to test whether it is safe to use > UTF-8 for encoding but use C collation, and then maybe investigate other > collations. > This worked: > createdb -E UTF-8 --lc-collate=C some_db > so it should be easy enough to play around with it some. I'm not sure how > to get a list of valid collations for any given charset, and it seems like C > collation would generate somewhat strange results with non-ascii characters > (french accented characters are supposed to alphabetize in some unexpected > manner, I believe), so there must be something better - closer to UTF-8 > collation but without ignoring whitespace and such. A quick google search > reveals that there is some kind of standard for unicode collation > (http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what > is represented by the en_US.UTF-8 collation or not. I've got no real > experience with this stuff. > It appears that there are differences regarding collation in recent versions > - the beta docs for 9.1 show that you can set collation on individual > operations or differently for different columns > (http://www.postgresql.org/docs/9.1/static/collation.html ). I see nothing > like that in 8.4 docs. > It definitely looks like we both need to have a read of the localization > chapter of the docs for our database version as there is a bunch of stuff in > there that I was surprised to read when I just did a quick scan - like using > anything but C or posix is much slower and can produce incorrect results in > a 'like' query > It looks like the docs prior to 9.1beta have no real reference to collation > at all, so it's down to trial and error unless someone in the know speaks > up. > --sam > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with selecting from a function
2011/6/21 Andreas : > Hi, > > I've got a table with a couple of objects. > Primary key object_id. > > There is a function that fetches some values from another table that relate > to an object_id. > Like fctX ( 7 ) --> set of typX ... ( 14, 'bla' ), ( 17, 'blu' ), ( 21, > 'ble' ), ... > The result of the function can have 0 or more lines of a defined result-type > typX. > Those resulting numbers are not object_ids. > > Now I'd need a SELECT that lists all function results of all object_ids. > Like: > ... > 6, ... > 7, 14, 'bla' > 7, 17, 'blu' > 7, 21, 'ble' > 8, ... > > Actually it was enough to get just the numerical column of the function > result. > > I tried > select object_id, fctX (object_id) from objects; > Then I get: > 7, (14, 'bla') > 7, (17, 'blu') > 7, (21, 'ble') <--- round brackets > This looks like an array but how can I split it up to columns or at least > extract the number-column? > this is composite value you can try SELECT object_id, (fctX(object_id)).* from objects Regards Pavel Stehule > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FOR EACH STATEMENT trigger ?
Hello it isn't bug. PostgreSQL doesn't support NEW and OLD tables like MSSQL does for statement triggers. Regards Pavel Stehule 2011/5/6 Frédéric BROUARD : > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving the pseudo table NEW (or > OLD) in the trigger function SQL statement. > > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID INT NOT NULL, > PDD_BEGIN DATE NOT NULL, > PDD_END DATE, > PDD_QUANTITY FLOAT NOT NULL); > > We want to never have more thant one PDD_END = NULL for the same PRD_ID. > > The assertion we can do is : > > ALTER TABLE T_PRODUIT_DISPO_PDD > ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL > CHECK (NOT EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1)) > > Which is not supported by PG > > So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a FOR > EACH ROW. > > Here is the code I try : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT COUNT(*) INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID IN(SELECT NEW.PRD_ID > FROM NEW) AS T > AND PDD_END IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple > de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > Which produce an error ! > > Of course I can do that with a FOR EACH STATEMENT like this one : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT 1 INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID = NEW.PRD_ID > AND PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple > de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > CREATE TRIGGER E_IU_PRD > AFTER INSERT OR UPDATE > ON T_PRODUIT_DISPO_PDD > FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); > > > But it is absolutly not that I Want > > Thanks > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.com * > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FOR EACH STATEMENT trigger ?
Hello, 2011/5/6 F. BROUARD / SQLpro : > > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving the pseudo table NEW > (or OLD) in the trigger function SQL statement. > PostgreSQL doesn't support NEW or OLD tables in statement triggers. You should to use ROW triggers. Regards Pavel Stehule > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID INT NOT NULL, > PDD_BEGIN DATE NOT NULL, > PDD_END DATE, > PDD_QUANTITY FLOAT NOT NULL); > > We want to never have more thant one PDD_END = NULL for the same PRD_ID. > > The assertion we can do is : > > ALTER TABLE T_PRODUIT_DISPO_PDD > ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL > CHECK (NOT EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1)) > > Which is not supported by PG > > So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a > FOR EACH ROW. > > Here is the code I try : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT COUNT(*) INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID IN(SELECT NEW.PRD_ID > FROM NEW) AS T > AND PDD_END IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > Which produce an error ! > > Of course I can do that with a FOR EACH STATEMENT like this one : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT 1 INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID = NEW.PRD_ID > AND PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > CREATE TRIGGER E_IU_PRD > AFTER INSERT OR UPDATE > ON T_PRODUIT_DISPO_PDD > FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); > > > But it is absolutly not that I Want > > Thanks > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.com * > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PLpgSQL variables persistance between several functions calls
Hello no, it's not possible Regards Pavel Stehule 2011/5/2 Charles N. Charotti : > Hello everybody ! > > I want to know if I could share PLpgSQL variables between different > functions and within different calls just using memory (not tables or other > methods). > > If it is really possible ? > > Thanks in advance, > > Chuck > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update with recursive query
Hello it is possible in 9.1. In older version you have to use a temp table. Regards Pavel Stehule 2011/4/14 Steven Dahlin : > Is it possible to execute an update using recursion? I need to update a set > of records and also update their children with the same value. I tried the > following query but it gave an error at the "update schema.table tbl": > > with recursive childTbl( pid, > ppid, > proc_id, > other_id ) > as ( select prc.pid, > prc.ppid, > prc.proc_id, > prc.other_id > from my_schema.prc_tbl prc > where ( ( prc.proc_path like '%stuff%' ) > or ( prc.proc_parameters like '%stuff%' ) ) > and ( prc.other_id is null ) > union all > select prcsub.pid, > prcsub.ppid, > prcsub.proc_id, > prcsub.other_id > from childTbl prcpar, > my_schema.prc_tbl prcsub > where ( prcsub.ppid = prcpar.pid ) > ) > update my_schema.prc_tbl prc > set other_id = 101 > from childTbl > > However, if I do a "select * from childTbl" it works. The docs take about > updates and talk about recursive queries with selects but nothing seems to > cover the joining of the two. > > Thanks > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Obscure behavior of ORDER BY
Hello
this behave depends on your language rules. So this behave can be ok.
pavel=# select * from (values('.'),('@'),('.xxx'),(' ')) x order by 1;
column1
─
.
@
.xxx
(4 rows)
you can se so string with space on start is on end and this is
correct, because spaces and white chars are ignored.
Regards
Pavel Stehule.
2011/3/21 Tambet Matiisen :
> Hi everyone!
>
> I recently noticed obscure behavior of ORDER BY. Consider this example:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.'::text as email
> ) a
> order by email;
>
> The result is:
> email
> ---
> .
> @
> (2 rows)
>
> This is all normal - I expect, that dot is smaller than ampersand. But if I
> add anything after dot, the order is reversed:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.m'::text as email
> ) a
> order by email
>
> The result is:
> email
> ---
> @
> .m
> (2 rows)
>
> Why is this happening? As dot is smaller than ampersand, anything after dot
> shouldn't matter.
>
> I'm using PostgreSQL 8.4.7 on 32-bit Debian.
>
> Thanks in advance,
> Tambet
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Retrieve the column values of a record without knowing the names
Hello you can't simply iterate over record in plpgsql. You can use a some toolkits like PLToolkit, or different PL language like PLPerl, or some dirty trick http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html regards Pavel Stehule 2011/2/16 arthur_info : > > Hello, > > I've got the following function and I want to access the fields values of my > record by index. The problem is that my select is retrieving each record > line with all values and not each one of each row on my view... How can I > solve this problem? > > Thanks in advance. > > > CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS > $BODY$ > DECLARE > reg record; > BEGIN > for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM > estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop > for j in 1..array_upper(reg.campos,1) loop > raise notice 'Field Value: %',reg.campos[j]; > end loop; > end loop; > return 'ok'; > END; > $BODY$ > > LANGUAGE plpgsql VOLATILE; > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387932p3387932.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function compile error
Hello please, look to page http://www.postgresql.org/docs/9.0/interactive/plpgsql-porting.html It can be faster, if you try to read PL/pgSQL documentation first. PL/pgSQL is near PL/SQL, but it is a different language and environment still. http://www.postgresql.org/docs/9.0/interactive/plpgsql.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am trying to create the function as below but it is throwing error > 'ERROR: syntax error at or near "DECLARE"', Could some one help me please > > CREATE FUNCTION check_password(databasename text, tablename text, indexname > text)RETURNS VOID AS > DECLARE v_count INTEGER; > BEGIN > select count(1) into v_count from pg_index inx where inx.indexrelid > in > (select oid from pg_class where relname=$3 and relowner in > (select oid from pg_authid where rolname=$1)) > and inx.indrelid in > (select oid from pg_class where relname=$2 and relowner in > (select oid from pg_authid where rolname=$1)); > if v_count = 0 then > execute immediate 'create unique index $3 on $2 (acn_id)'; > end if; > END; > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > <>
Re: [SQL] Oracle Equivalent queries in Postgres
2011/2/16 Thomas Kellerer : > Pavel Stehule, 16.02.2011 11:50: >> >> Try to use a standardized information_schema instead - these views are >> same on PostgreSQL and Oracle. > > Unfortunately they are not the same: Oracle does not support > INFORMATION_SCHEMA > sorry, I expected so all mature databases support it. Regards Pavel > Regards > Thomas > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Oracle Equivalent queries in Postgres
hello list of tables http://www.postgresql.org/docs/current/static/infoschema-tables.html information about column http://www.postgresql.org/docs/current/static/infoschema-columns.html information about indexes - it's not part of ANSI/SQL so you have to look to pg_index or pg_indexes. Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hi Pavel, > In the given link, there are no views which can give information about > indexes. > > Is it possible for you to give me the equivalent queries in postgres? > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > > On 2/16/2011 4:20 PM, Pavel Stehule wrote: > > Hello > > PostgreSQL uses a different system tables than Oracle. Try to use a > standardized information_schema instead - these views are same on PostgreSQL > and Oracle. > > http://www.postgresql.org/docs/current/static/information-schema.html > > Regards > > Pavel Stehule > > > > > 2011/2/16 Sivannarayanreddy > >> Hello, >> I am checking the compatibility of my product with Postgres database and i >> stucked in forming the below oracle equivalent queries in Postgres database, >> Could some one help me pleaseee >> >> 1) Trying to get index and corresponding columns information of all the >> tables in mentioned schema >> >> select inx.table_name as table_name, inx.index_name as >> index_name,column_name, case ( when inx.index_type = 'IOT - TOP' then 'Y' >> else 'N' end, >> case >> when inx.uniqueness = 'UNIQUE' then 'Y' >> when inx.uniqueness = 'NONUNIQUE' then 'N' >> end, >> 'N' as ignore_dup_key, >> cast(inc.column_position as NUMBER(10)) >> fromall_indexes inx, >> all_ind_columns inc >> where inx.owner = '" + database.toUpperCase() + >> "' >> and inx.table_name = inc.table_name >> and inx.index_name = inc.index_name >> and inx.owner = inc.index_owner >> and inx.owner = inc.table_owner >> and inx.dropped = 'NO' >> and inx.table_name = '" + tableName.toUpperCase() + "' >> order by inx.table_name, inx.index_name, cast(inc.column_position as >> NUMBER(10)) >> >> >> 2) Trying to get the columns information of all the tables in mentioned >> schema >> >> select tab.TABLE_NAME, >> col.COLUMN_NAME, >> col.DATA_TYPE, >> cast(case when col.CHAR_COL_DECL_LENGTH is NULL then >> col.DATA_PRECISION else col.CHAR_LENGTH end as NUMBER(10)), >> cast(col.NULLABLE as CHAR(1)), >> cast(col.COLUMN_ID as NUMBER(10)) >> >> fromall_tab_columnscol, >> all_tables tab >> where tab.TABLE_NAME= col.TABLE_NAME >> and tab.OWNER = col.OWNER >> and tab.OWNER = '" + database.toUpperCase() + "' >> and tab.DROPPED = 'NO' >> and tab.TABLE_NAME = '" + tableName.toUpperCase() + "' >> order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10)) >> >> >> >> *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* >> >> Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, >> Bangalore – 560037, India. >> *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 >> ; >> >> *Email:* sivannarayanre...@subexworld.com ; * >> URL:* www.subexworld.com >> >> >> >> *Disclaimer: This e-mail is bound by the terms and conditions described >> at >> **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> >> > > <><>
Re: [SQL] Oracle Equivalent queries in Postgres
Hello PostgreSQL uses a different system tables than Oracle. Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle. http://www.postgresql.org/docs/current/static/information-schema.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am checking the compatibility of my product with Postgres database and i > stucked in forming the below oracle equivalent queries in Postgres database, > Could some one help me pleaseee > > 1) Trying to get index and corresponding columns information of all the > tables in mentioned schema > > select inx.table_name as table_name, inx.index_name as > index_name,column_name, case ( when inx.index_type = 'IOT - TOP' then 'Y' > else 'N' end, > case > when inx.uniqueness = 'UNIQUE' then 'Y' > when inx.uniqueness = 'NONUNIQUE' then 'N' > end, > 'N' as ignore_dup_key, > cast(inc.column_position as NUMBER(10)) > fromall_indexes inx, > all_ind_columns inc > where inx.owner = '" + database.toUpperCase() + "' > > and inx.table_name = inc.table_name > and inx.index_name = inc.index_name > and inx.owner = inc.index_owner > and inx.owner = inc.table_owner > and inx.dropped = 'NO' > and inx.table_name = '" + tableName.toUpperCase() + "' > order by inx.table_name, inx.index_name, cast(inc.column_position as > NUMBER(10)) > > > 2) Trying to get the columns information of all the tables in mentioned > schema > > select tab.TABLE_NAME, > col.COLUMN_NAME, > col.DATA_TYPE, > cast(case when col.CHAR_COL_DECL_LENGTH is NULL then > col.DATA_PRECISION else col.CHAR_LENGTH end as NUMBER(10)), > cast(col.NULLABLE as CHAR(1)), > cast(col.COLUMN_ID as NUMBER(10)) > > fromall_tab_columnscol, > all_tables tab > where tab.TABLE_NAME= col.TABLE_NAME > and tab.OWNER = col.OWNER > and tab.OWNER = '" + database.toUpperCase() + "' > and tab.DROPPED = 'NO' > and tab.TABLE_NAME = '" + tableName.toUpperCase() + "' > order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10)) > > > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > <>
Re: [SQL] PL/SQL block error
Hello you should to wrap code to function or inline function everywhere. psql doesn't support PL/SQL and doesn't support inlined PL/SQL blocks. Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am very new to the postgres sql, i am trying to execute below pl/sql > block in postgres but i am getting error *'ERROR: syntax error at or near > "integer" '*, Could some one help me in this regard > > declare > v_count integer; > begin > select count(1) into v_count from pg_index inx where inx.indexrelid in > (select oid from pg_class where relname='action_pk' and relowner in > (select oid from pg_authid where rolname='postgres_ref')) > and inx.indrelid in > (select oid from pg_class where relname='action' and relowner in > (select oid from pg_authid where rolname='postgres_ref')); > > if v_count = 0 then > execute immediate 'create unique index action_pk > on action(acn_id)'; > end if; > end > > > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> >
Re: [SQL] Determine length of numeric field
Hello probably you have to use a explicit cast postgres=# select length(10::numeric::text); length 2 (1 row) Regards Pavel Stehule 2011/2/15 Tony Capobianco : > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > > dw=# \d uniq_hits > Table "support.uniq_hits" > Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate | date | > total | numeric | > hitdate_id | integer | > Indexes: > "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); > > I haven't had much luck with the length or char_length functions on > postgres. > > Thanks. > Tony > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
2011/2/8 Emi Lu : > On 02/08/2011 02:51 PM, Rolando Edwards wrote: >> >> SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where >> A.c1=B.c1),', ') from T1 A order by c1; >> >> Give it a Try !!! > > > Thanks a lot! Very helpful! > > array_to_string() + array() is exactly what I am looking for! > > I just wonder that array_to_string() + array() will provide me good > performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be a best. Regards Pavel Stehule the best speed gives a string_agg, but it is only in 9.0 > > Thanks again! > -- > Lu Ying > > > > > >> Rolando A. Edwards >> MySQL DBA (SCMDBA) >> >> 155 Avenue of the Americas, Fifth Floor >> New York, NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM& Skype : RolandoLogicWorx >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org >> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu >> Sent: Tuesday, February 08, 2011 2:36 PM >> To: pgsql-sql@postgresql.org >> Subject: [SQL] "select c1, method(c2) group by c1" returns all values of >> c2 for c1 >> >> Good afternoon, >> >> Is there a method to retrieve the following results: >> >> T1(c1 int, c2 varchar(128) ) >> - >> >> >> (1, val1); >> (1, val2); >> (1, val3); >> (2, val1); >> (3, val5); >> (3, val6); >> >> select c1, method(c2) >> group by c1 >> >> returns: >> >> 1, "val1, val2, val3" >> 2, "val1" >> 3, "val5, val6" >> >> >> Thanks a lot! >> >> -- >> Lu Ying >> > > > -- > Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 > em...@encs.concordia.ca +1 514 848-2424 x5884 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu : > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > - > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UTF characters compromising data import.
Hello 2011/2/8 Gavin Beau Baumanis : > Hi Everyone, > > I am trying to import some data (provided to us from an external source) from > a CSV file using "\copy " > > But I get the following error message; > invalid byte sequence for encoding "UTF8": 0xfd > HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > > I understand the error message - but what I don't know is what I need to set > the encoding to - in order to import / use the data. > is impossible to import data without knowledge of encoding. you can use a some utils, that try to select a encoding http://linux.die.net/man/1/enca Regards Pavel Stehule > As always - thanks in advance for any help you might be able to provide. > > > Gavin "Beau" Baumanis > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using of select (myfunction()).* is so slow
>> > > M ok Thanks...So there is no workaround/alternative to this? > yes, (SELECT x.* from func(...) x) instead SELECT (func(...)).* regards Pavel Stehule > Gerardo > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how control update rows
Hello 2011/2/3 Sabin Coanda : > Hi there, > > I'd like to control the rows which are updated. I found useful the option > RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there > ? > > I think to something like that: > > SELECT * > FROM ( > UPDATE "T" SET > "C" = 1 > WHERE "ID" > 100 > RETURNING * > ) x > It's not implemented yet. You can use a stored procedure or temp tables instead. Regards Pavel Stehule > TIA, > Sabin > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using of select (myfunction()).* is so slow
Hello If you use a record expansion over function's result, then function is called once for record's field. so don't do it on slow functions. Regards Pavel 2011/2/3 Gerardo Herzig : > Hi all, im using a function of my own in a subquery, and when wonderig > about the slowliness of this one, y relalize that: > > test=# SELECT (_xxfunction(854,'711H',11,false)).* ; > (4 filas) > --Result DELETED > Duración: 1069,465 ms > > > glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; > (4 filas) > Duración: 228,699 ms > > For privacy reasons, i just deleted the result and the function name, > but the resulst are obviously exactly the same, and the ().* form (which > i needed) is taking so much more...there is a reason why? A workaround? > > Thanks! > > Gerardo > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] aggregation of setof
Hello
use a array constructor instead
SELECT ARRAY(SELECT ...)
Regards
Pavel Stehule
2011/1/31 Andreas Gaab :
> Functions apparently cannot take setof arguments.
>
>
>
> Postgres 8.4:
>
>
>
> CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
>
> RETURNS anyarray AS
>
> $BODY$
>
> SELECT $1 LIMIT 1;
>
> $BODY$
>
> LANGUAGE 'sql' STABLE;
>
>
>
> à
>
> ERROR: functions cannot accept set arguments
>
>
>
>
>
>
>
> Von: Viktor Bojović [mailto:viktor.bojo...@gmail.com]
> Gesendet: Samstag, 29. Januar 2011 09:28
> An: Andreas Gaab
> Betreff: Re: [SQL] aggregation of setof
>
>
>
> i have never used that type but maybe you can try this;
>
> -create function which returns text[], and takse setof text as argument (if
> possible)
>
> -reach every text[] in set of text[] using array index
>
> -return values using "return next" for each text in text[] which is in set
> of text[]
>
>
>
> On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab wrote:
>
> Hi all,
>
>
>
> I would like to write a query, which aggregates the results of
> regexp_matches(). The problem is that regexp_matches returnes setof text[]
> as documented even if I discard the global flag
> (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> ). Thus resulting in an error when I try to aggregate the result:
>
>
>
> “
>
> SELECT array_accum(
>
> regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
>
> )
>
> ---
>
> ERROR: set-valued function called in context that cannot accept a set
>
> ** Fehler **
>
> ERROR: set-valued function called in context that cannot accept a set
>
> SQL Status:0A000
>
> “
>
>
>
> Can I convert a ‚setof text[]‘ to a ‚text[]‘?
>
>
>
> Alternatively I could use a sub-select, but I am curious if there are other
> solutions around.
>
>
>
> Regards,
>
> Andreas
>
>
>
> ___
>
>
>
> SCANLAB AG
>
> Dr. Andreas Simon Gaab
>
> Entwicklung • R & D
>
>
>
> Siemensstr. 2a • 82178 Puchheim • Germany
>
> Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
>
> mailto:a.g...@scanlab.de • www.scanlab.de
>
>
>
> Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
>
> Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
>
> Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
>
> ___
>
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule 2010/12/30 Gary Stainburn : > Hi folks, > > I'm writing my first plpsql function in ages and I'm going blind trying to see > why it won't create. The error message and the code are below. I'm guessing > it's something blindingly obvious, but can someone please point it out to me. > > ta > > Gary > > The actual line number is the LANGUAGE line at the end of the file. > > goole=# \i project_delivery_date.sql > psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";" > LINE 37: END; > ^ > goole=# \q > [r...@stan t-cards]# cat project_delivery_date.sql > -- vim: ft=sql et ai ic > -- > -- project_delivery_date() - project delivery date from existing dates > > CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$ > DECLARE > eta_dealer ALIAS FOR $1; > eta_customer ALIAS FOR $2; > req_date ALIAS FOR $3; > act_date ALIAS FOR $4; > eta date; > > BEGIN > IF act_date IS NOT NULL THEN > return act_date; > END IF; > IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN > eta := eta_dealer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN > eta := eta_customer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN > eta := NULL; > ELSE IF eta_dealer > eta_customer THEN > eta := eta_dealer; > ELSE > eta := eta_customer; > END IF; > IF eta IS NOT NULL AND req_date IS NULL THEN > RETURN eta; > END IF; > IF eta IS NULL AND req_date IS NOT NULL THEN > RETURN req_date; > END IF; > IF eta IS NULL AND req_date IS NULL THEN > RETURN NULL; > END IF; > IF eta > req_date THEN > RETURN eta; > END IF; > RETURN req_date; > END; > $$ LANGUAGE 'plpgsql'; > > [r...@stan t-cards]# > > -- > Gary Stainburn > I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import
Hello 2010/12/21 Viktor Bojović : > Hi, > > can anyone recommend me a windows and linux free tools for importing data > into postgre. > Source files are CSV or excel. PostgreSQL can read a CVS files via a COPY statement. You can use a \copy metacommand too from psql Regards Pavel Stehule > Thanx in advance > -- > --- > Viktor Bojović > --- > Wherever I go, Murphy goes with me > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to convert string to integer
Hello you can use a ::int for converting to integer. Or better - you can alter column to integer. It will be faster and more correct. Regards Pavel Stehule 2010/12/15 venkat : > Dear All, > How do i convert string to int > select SUM(pan_1) from customers1 where name='101' > When i run the above query i m getting "function sum(character varying) > does not exist".. > Please anyone can guide me.. > Thanks > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] conditional aggregates
Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk : > Hi list, > Can anyone advise me on creating an aggregate that would take additional > parameter as a condition ? For example, say I have a table like this > id;value > 1;45 > 2;13 > 3;0 > 4;90 > I'd like to do something like this > SELECT min_some_cond_aggregate(value,0) FROM table > to get the minimal value from table greater than 0, in this case 13. > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess my > other computations. My current solution involves a function operating on the > output of array_accum from the docs, but I'm looking for more elegant > solution. > Is this possible at all ? I'm running 8.1. > > regards > mk > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] error null value in column" concat_id" violates not-null constraint
Hello 2010/11/25 Ana Louro : > Hi, > I'm just beggining in PostgreSql 9.0 > > I've created a table ,like this: > > CREATE TABLE auxiliar > ( > ano integer, > codigodc character varying, > id character varying, > concat_id character varying NOT NULL, > CONSTRAINT concat PRIMARY KEY (concat_id); > > Now i want to insert values on concat_id resulting from a function > called "concat_id" > > INSERT INTO concat_id > SELECT(concat_id) FROM auxiliar ; insert has a syntax: INSERT INTO function call has a syntax funcname(parameters) so if I would to fill table auxiliar from some function, then I'll use a statement INSERT INTO auxilar SELECT * FROM funcname(..) Regards Pavel Stehule > > I get "error null value in column"concat_id" violatres not null > constraint > > > > Could anyone tell me what am i doing wrong? > > Ana > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help with plpgsql function.
2010/11/14 Adrian Klaver : > On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote: > >> > } >> >> Hello >> >> you can use a RETURN QUERY statement - some like >> >> CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int) >> RETURNS SETOF RECORD AS $$ >> BEGIN >> IF i = 1 THEN >> RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40; >> ELSE >> RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90; >> END IF; >> RETURN; >> END; >> $$ LANGUAGE plpgsql; >> >> SELECT * FROM foo(1); >> SELECT * FROM foo(2); >> >> Regards >> >> Pavel Stehule >> > > FYI the OP is using 8.2 :) RETURN QUERY is 8.3+ sorry :) then RETURN QUERY query --> DECLARE r record; BEGIN FOR r IN SELECT RETURN NEXT r; END FOR; ... Regards Pavel Stehule > > -- > Adrian Klaver > adrian.kla...@gmail.com > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help with plpgsql function.
2010/11/14 berelith :
>
> Hi,
>
> I'm creating the function on a postgres 8.2 server.
> I would like the function to accept half a dozen varied parameters (varchars
> and timestamps).
> The first parameter will determine which one of the 6 different select
> queries that function is going to run.
>
> The function will return all the rows from the chosen select statement.
>
> I've been reading the postgresql documentation in creating functions that
> returns rowset and I've read about plpgsql. It seems to be what I need
> because there is going to be conditional statements.
>
> I'm just having a hard time putting it all together, and I'm not sure about
> the syntax and how to return the selected rows back into OUT parameters.
>
> This is a short pseudo example:
>
> CREATE OR REPLACE FUNCTION report (
> -- function arguments, type will determine which one of the 6 queries to run
> IN type character varying(20),
> IN param1 character varying(255),
> IN param2 timestamp,
> -- returned values
> OUT code table.code%TYPE,
> OUT name table.name%TYPE
> )
> RETURNS SETOF rows
> { LANGUAGE PLPGSQL
> IF type like 'type A' THEN
> SELECT code, name INTO rows FROM tableA join some table ;
> return rows
> ELSIF type like 'type B' THEN
> SELECT code, name INTO rows FROM tableB join someothertable ... ;
> return rows
> ELSE
> RETURN VOID
> END IF;
> }
>
>
Hello
you can use a RETURN QUERY statement - some like
CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int)
RETURNS SETOF RECORD AS $$
BEGIN
IF i = 1 THEN
RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40;
ELSE
RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo(1);
SELECT * FROM foo(2);
Regards
Pavel Stehule
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Need-help-with-plpgsql-function-tp3264047p3264047.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trailing spaces - is there an easier way?
2010/11/4 Dean Gibson (DB Administrator) : > I'm sure this has been asked before, but I couldn't find it: > > I have a "zzz CHAR (8)" field. It needs to be CHAR because trailing spaces > need to be ignored for most operations. However, I need to concatenate it > with another (literal) string and need the spaces to be significant in that > operation. The ONLY WAY I could find to do it in v9.0.1 was (represented > in the following function): > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > RETURNS NULL ON NULL INPUT > IMMUTABLE > LANGUAGE SQL AS $SQL$ > SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > $SQL$; > > And then of course I write: > > SELECT padded( zzz ) || '/' || ... > > Is there a better way? > > nic=# SELECT ''::char(6) || ''; ?column? -- (1 row) Time: 2.710 ms nic=# SELECT ''::char(6)::cstring || ''; ?column? (1 row) regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert into table from list or array ?
Hello
2010/10/18 Andreas :
> Hi,
>
> is it possible to insert into a table from list or an array ?
>
yes, it's possible
INSERT INTO tmptab
SELECT v
FROM unnest(string_to_array('1,2,4,2,1',',')) g(v)
Regards
Pavel Stehule
> Suppose there is a set of numbers that might be IDs of tables within the DB.
> To work with them I'd need a temporary table that just holds a single column
> with those numbers.
> Something like
>
> create temporary table tmptable as select id from
> ( 2, 3, 5, 7, 11, 13, 17, 19, 23, 31,
> 37, ... );
>
> would be great.
>
> I get those numbers as textfile with 10 numbers per line and devided by
> comma+space as in the sample above, though the comma+space is negotiable if
> this were an issue.
>
> :)
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is there a conditional string-concatenation ?
Hello
more simply
postgres=# CREATE OR REPLACE FUNCTION public.foo(a text, b text, c text)
RETURNS text
LANGUAGE sql
AS $function$
SELECT coalesce($1 || $2 || $3,
$1 || $2,
$2 || $3)
$function$
Regards
Pavel Stehule
2010/10/12 Osvaldo Kussama :
> 2010/10/12 Andreas :
>> Hi,
>> Is there a conditional string-concatenation ?
>>
>> I'd like to have an elegant way to connect 2 strings with some 3rd element
>> between only if there really are 2 strings to connect.
>>
>> e.g.
>> MyCat ( 'John', '_', 'Doe' ) --> 'John_Doe'
>> while
>> MyCat ( 'John', '_', '' ) --> 'John'
>> MyCat ( '', '_', 'Doe' ) --> 'Doe'
>> MyCat ( '', '_', '' ) --> NULL
>>
>> It should treat NULL and '' equally as empty
>> and it should trim each of the 3 elements.
>>
>> so
>> MyCat ( ' John ', '_', NULL ) --> 'John'
>> MyCat ( 'John', NULL, 'Doe' ) --> 'JohnDoe'
>>
>
>
> Try:
> bdteste=# SELECT nullif(ltrim(rtrim(coalesce(c1,'') || coalesce(c2,'')
> || coalesce(c3,''),' _'),' _'),'')
> bdteste-# FROM (VALUES ('John', '_', 'Doe'),('John', '_', ''),('',
> '_', 'Doe'),('', '_', ''),(' John ', '_', NULL),('John',
> NULL, 'Doe')) AS foo(c1,c2,c3);
> nullif
> --
> John_Doe
> John
> Doe
>
> John
> JohnDoe
> (6 rows)
>
> Osvaldo
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and document views and user functions
hello 2010/8/30 Peter Steinheuser : > You'll probably have to write something (a function) that pulls the data out > of pg_catalog. > You can get a leg up on that by connecting to psql using -E, which echoes > hidden queries. > If you do a \df+ on a function, you'll see the query PG uses. > there is much more easy way to get a function source code SELECT pg_catalog.pg_get_functiondef(oid) Regards Pavel Stehule > ex. > production=# \df+ myschema.* > > * QUERY ** > SELECT n.nspname as "Schema", > p.proname as "Name", > pg_catalog.pg_get_function_result(p.oid) as "Result data type", > pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", > CASE > WHEN p.proisagg THEN 'agg' > WHEN p.proiswindow THEN 'window' > WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN > 'trigger' > ELSE 'normal' > END as "Type", > CASE > WHEN p.provolatile = 'i' THEN 'immutable' > WHEN p.provolatile = 's' THEN 'stable' > WHEN p.provolatile = 'v' THEN 'volatile' > END as "Volatility", > pg_catalog.pg_get_userbyid(p.proowner) as "Owner", > l.lanname as "Language", > p.prosrc as "Source code", > pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" > FROM pg_catalog.pg_proc p > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace > LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang > WHERE n.nspname ~ '^(myschema)$' > ORDER BY 1, 2, 4; > ** > > > > On Mon, Aug 30, 2010 at 2:21 PM, David Harel wrote: >> >> Hi, >> >> I am looking for an easy way to backup views and functions. I want to >> store them in our version control system. >> >> Using pgAdmin I can access them one at a time. I am looking for a better >> reporting mechanism. psql shell command for such report will be just fine. >> >> Sorry for the lame question. I didn't find any clues on the web >> .(typically, I fail to phrase the right keywords) >> >> -- >> Thanks. >> >> David Harel, >> >> == >> >> Home office +972 77 7657645 >> Cellular: +972 54 4534502 >> Snail Mail: Amuka >> D.N Merom Hagalil >> 13802 >> Israel >> Email: harel...@ergolight-sw.com >> > > > > -- > Peter Steinheuser > psteinheu...@myyearbook.com > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and document views and user functions
Hello 2010/8/30 David Harel : > Hi, > > I am looking for an easy way to backup views and functions. I want to store > them in our version control system. > move your functions and view to separate schema - and do backup with pg_dump -n schema regards Pavel Stehule > Using pgAdmin I can access them one at a time. I am looking for a better > reporting mechanism. psql shell command for such report will be just fine. > > Sorry for the lame question. I didn't find any clues on the web .(typically, > I fail to phrase the right keywords) > > -- > Thanks. > > David Harel, > > == > > Home office +972 77 7657645 > Cellular: +972 54 4534502 > Snail Mail: Amuka > D.N Merom Hagalil > 13802 > Israel > Email: harel...@ergolight-sw.com > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Boolean output representation.
Hello
2010/8/26 Dmitriy Igrishin :
> Hey all,
>
> As is known, there are many valid literal values for the "true"
> and "false" state of boolean data type.
> True whether that output of boolean type shows only using
> the letters 't' and 'f' or its possible to change the output representation,
> e.g., to "true" or "false" ("1", "0")?
>
no, it isn't possible - you can write a simple formating function or
own custom data type.
Regards
Pavel Stehule
> Regards,
> Dmitriy
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql out parameter with select into
Hello It cannot work, you mix the sql with plpgsql language 2010/8/17 Imre Horvath : > Hi! > > My question is, how can I get the out parameter from a function with > SELECT INTO by name? > I mean: > > create function testfunc1(OUT _status integer) returns integer as > $BODY$ > _status := 0; > $BODY$ > language plpgsql; > > create function testfunc2() as > declare > status integer; > $BODY$ > select into status * from testfunc1(); > $BODY$ > language plpgsql; > > create function testfunc3() as > declare > status integer; > $BODY$ > select into status _status from testfunc1(); > $BODY$ > language plpgsql; > > testfunc2 works, testfunc3 not. > > Thanks in advance: > Imre Horvath > > create or replace function test1(out _status integer) returns integer as $$ begin _status := 10; end; $$ language plpgsql; create or replace function test3() returns void as $$ declare status integer; begin select into status _status from test1(); raise notice '%', status; end; $$ language plpgsql; this working for me. postgres=# select test3(); NOTICE: 10 test3 ─── (1 row) Regards Pavel Stehule > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How would I store little key-Nr to text lists?
2010/7/10 Andreas : > Hi > > how would I store little key-Nr to text lists? > Like e.g. > > colors > 1 red > 2 green > 3 blue > > Maybe I later also need to add 4 yellow? > > Obviously the numbers are used for foreign keys in data tables and the texts > appear in selects. > On the other hand users should chose from listboxes in an application so I > need to be able to read the (key, ext) tupels. > > For now I use 2 column tables that in selects get joined to a data table. > > Is there a more clever way ? you can use enums, you can use a hstore contrib module, but what you doing is best regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
2010/7/5 silly sad : > On 07/05/10 11:18, Pavel Stehule wrote: > >>> P.S. >>> Practically for storing pictures i prefer regular files. >>> >> >> how I say - it depends on application - sometime can be useful have to >> access to all data only from db connect - for million small pictures >> the bytea can be best. > > i really love postgres TEXT type, but i hate CSTRING input-output why? and it isn't true - you can use a binary interface of PQexecParams - minimally for text and bytea it is very simple Pavel > bottleneck. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
2010/7/5 silly sad : > On 07/05/10 11:03, Pavel Stehule wrote: >> >> 2010/7/5 silly sad: >>> >>> On 07/05/10 10:43, Pavel Stehule wrote: >>> >>>> The good size for text or bytea is less than 100M and real max isn't >>>> 2G but it is 1G. LO isn't these limits because it isn't accessable on >>>> SQL level. >>> >>> any regular file on my filesystem isn't accessible on SQL level. >>> i am happy with them and never tried to store at a database. >> >> this is second extreme - you can use everything if you know what you >> do - and mainly it depends on applications and requests that you have >> to solve. > > the trouble is the initiator of the thread didn't determine what is his > problem either storing of a zero-byte containing string or storing of huge > strings. > I answered him about BYTEA and he replied about BLOB. > I only tried to say i didn't say a word about BLOB. yes > > P.S. > Practically for storing pictures i prefer regular files. > how I say - it depends on application - sometime can be useful have to access to all data only from db connect - for million small pictures the bytea can be best. Pavel -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
2010/7/5 silly sad : > On 07/05/10 10:43, Pavel Stehule wrote: > >> The good size for text or bytea is less than 100M and real max isn't >> 2G but it is 1G. LO isn't these limits because it isn't accessable on >> SQL level. > > any regular file on my filesystem isn't accessible on SQL level. > i am happy with them and never tried to store at a database. this is second extreme - you can use everything if you know what you do - and mainly it depends on applications and requests that you have to solve. Pavel > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
