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
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 cons
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:
>>> >
>>> >
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 '
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 hav
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'
)
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);
xperc
arge.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 l
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
ortTable) 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
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
>
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
>
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
cally 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
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 t
>
> 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)
>
> goo
(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
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 nee
e 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,
>
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
>&
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,
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 :
&g
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
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
&
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
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 kn
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
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/questi
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
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
>
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.
>
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 -
es$# 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
EPTION '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)
post
ACE 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.
&g
em 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
s?
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
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)
Re
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
(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 Stehu
>
> 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
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 thi
>> 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 w
" 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 introdu
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 .
R 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-fisca
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 li
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, "
der 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
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) :
>
>
th: 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 sta
> 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
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/
>>
> 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)
Ti
t
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:
)
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@po
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 sim
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
>&g
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: in
rá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
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 wa
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 varch
> 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
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
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
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).
>
>
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
>
his 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
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 :
>
/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(databasen
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
>
gards
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(Mone
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
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 p
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 er
>
> 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
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 follow
rder 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.
&g
>>
>
> 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
gt; 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
>
>
>
> -
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
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)
>
>
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 tryi
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
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
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 hav
cname(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
>
>
&g
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)
>&g
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.nab
| '';
?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
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
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
;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
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
> U
' 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,
> Dmi
nteger
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()
, 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 mail
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
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
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
1 - 100 of 251 matches
Mail list logo
