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 vic...@caido.ro
Hello
When I run :
SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min
Hello
2013/4/20 JORGE MALDONADO jorgemal1...@gmail.com
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
Hello
2013/3/26 Ben Morrow b...@morrow.me.uk:
Quoth pavel.steh...@gmail.com (Pavel Stehule):
Dne 25.3.2013 23:51 Ben Morrow b...@morrow.me.uk 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
2013/3/26 Pavel Stehule pavel.steh...@gmail.com:
Hello
2013/3/26 Ben Morrow b...@morrow.me.uk:
Quoth pavel.steh...@gmail.com (Pavel Stehule):
Dne 25.3.2013 23:51 Ben Morrow b...@morrow.me.uk napsal(a):
I would use a view for this:
create view vale_any as
select 'P'::text
..', CASE WHEN ctip = 'P'
THEN 'valapag' ELSE 'valerec' END)
LOOP
..
END LOOP;
Regards
Pavel Stehule
2013/3/25 Mauricio Cruz c...@sygecom.com.br:
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
$
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
---
hello4%/hello
(1 row)
Regards
Pavel
Hello
select (fce(..)).column from ...
or select column from fce()
Regards
Pavel Stehule
2013/1/31 Nei Rauni Santos nra...@gmail.com:
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
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 pavel.steh...@gmail.com
wrote:
fce
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
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 karl.g...@gmail.com:
I have a related problem and tried the PERFORM...EXECUTE pattern
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
2012/11/27 Thomas Kellerer spam_ea...@gmx.net:
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
(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
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 ramkumar.ye...@siemens.com:
Hi All,
I am facing a issue
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=#
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
Hello
try to use cursors
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
Regards
Pavel Stehule
2012/4/24 Andreas maps...@gmx.net:
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
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 pavel.steh...@gmail.com:
Hello
please try:
postgres=# create or replace function foo()
returns void
), but it is
good to use it to increase readability.
Regards
Pavel
thanks again
Tom
2012/4/19 Pavel Stehule pavel.steh...@gmail.com:
2012/4/19 thomas veymont thomas.veym...@gmail.com:
hi Pavel,
thanks for your answer,
I don't understand exactly how y should be declared, and how it
should
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 thomas.veym...@gmail.com:
(sorry my previous email
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 cesar.cast.m
Hello
see
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code
Regards
Pavel Stehule
2012/4/10 JORGE MALDONADO jorgemal1...@gmail.com:
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
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
2012/4/6 Rob Sargent robjsarg...@gmail.com:
On 04/06/2012 01:23 PM, Pavel Stehule wrote:
Hello
2012/4/6 Andreasmaps...@gmx.net:
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
Hello
use a refcursors
http://www.postgresql.org/docs/9.1/static/plpgsql-cursors.html
Regards
Pavel Stehule
2012/4/4 La Chi the_man.in...@yahoo.com:
hi every one
i have created this simple function which returns a column of table , i have
used simple SELECT statement , i simply want
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 jo...@jfcomputer.com:
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
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
Hello
2012/3/1 Peter Faulks faul...@iinet.net.au:
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
Hello
2012/2/2 F. BROUARD / SQLpro sql...@club-internet.fr:
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
=# 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
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
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
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
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
2011/11/10 tlund79 thomas.l...@eniro.no:
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
://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
- 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
2011/10/22 David Johnston pol...@yahoo.com:
On Oct 22, 2011, at 10:07, Pavel Stehule pavel.steh...@gmail.com wrote:
2011/10/22 David Johnston pol...@yahoo.com:
On Oct 22, 2011, at 6:41, Linos i...@linos.es wrote:
Hi all,
i need a little of advice on what could be the best way to store
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
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
/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
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 d.k...@imperial.ac.uk:
Hi,
I am trying to write a function that contains a cursor and iteratively calls
itself.
It is along the lines
Hello
this is not built in MSSQL, but PostgreSQL has a generate_series function
Regards
Pavel Stehule
2011/8/16 msi77 ms...@yandex.ru:
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 hongyuan1
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 jmen...@andago.com
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where
.
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
On 10 July 2011 21:20, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
2011/7/10 Uwe Bartels uwe.bart...@gmail.com:
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
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 chestercyo...@yahoo.com
what is the best performance / best practices
), itoa(i));
exec('commit');
Regards
Pavel Stehule
--- On Mon, 6/27/11, Pavel Stehule pavel.steh...@gmail.com wrote:
From: Pavel Stehule pavel.steh...@gmail.com
Subject: Re: [SQL] best performance for simple dml
To: chester c young chestercyo...@yahoo.com
Cc: pgsql-sql@postgresql.org
Date
, 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 pavel.steh...@gmail.com* wrote:
From: Pavel Stehule pavel.steh...@gmail.com
Subject: Re: [SQL] best
2011/6/23 Peter Eisentraut pete...@gmx.net:
On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote:
2011/6/22 Peter Eisentraut pete...@gmx.net:
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
Hello
a equalent of C collate for UTF8 is ucs_basic
Regards
Pavel Stehule
2011/6/22 Samuel Gendler sgend...@ideasculptor.com:
On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde e...@impactsoft.co.il wrote:
the database collation is: en_US.UTF-8
drop table t1;
create table t1 (recid int ,f1
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
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
2011/6/22 Peter Eisentraut pete...@gmx.net:
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:
?
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
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 broua...@club-internet.fr:
Hi there
I am trying to get an example of SET BASED trigger logic with FOR EACH
STATEMENT, but I cannot find
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
Hello
no, it's not possible
Regards
Pavel Stehule
2011/5/2 Charles N. Charotti ccharo...@yahoo.com:
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
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 pgdb.sldah...@gmail.com:
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
chars are ignored.
Regards
Pavel Stehule.
2011/3/21 Tambet Matiisen tambet.matii...@gmail.com:
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
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 arthur_i
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 sivannarayanre...@subexworld.com
Hello,
I am very new to the postgres sql, i am trying
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
Pavel Stehule
2011/2/16 Sivannarayanreddy sivannarayanre...@subexworld.com
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
2011/2/16 Thomas Kellerer spam_ea...@gmx.net:
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
/plpgsql.html
Regards
Pavel Stehule
2011/2/16 Sivannarayanreddy sivannarayanre...@subexworld.com
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
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 tcapobia...@prospectiv.com:
I'm altering datatypes in several tables from numeric to integer. In
doing so, I get
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
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 em...@encs.concordia.ca:
Good afternoon,
Is there a method to retrieve
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
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
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 gher...@fmed.uba.ar:
Hi all, im using a function of my own in a subquery, and when wonderig
about the
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
Hello
use a array constructor instead
SELECT ARRAY(SELECT ...)
Regards
Pavel Stehule
2011/1/31 Andreas Gaab a.g...@scanlab.de:
Functions apparently cannot take setof arguments.
Postgres 8.4:
CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
RETURNS anyarray AS
$BODY
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 gary.stainb...@ringways.co.uk:
Hi folks,
I'm writing my first plpsql function in ages and I'm going blind
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
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 ven.tammin...@gmail.com:
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 jankes...@gmail.com:
Hi list,
Can anyone advise me on creating an aggregate that would take additional
parameter as a condition ? For example, say
some function, then I'll use
a statement
INSERT INTO auxilar
SELECT * FROM funcname(..)
Regards
Pavel Stehule
I get error null value in columnconcat_id violatres not null
constraint
Could anyone tell me what am i doing wrong?
Ana
--
Sent via pgsql-sql mailing list (pgsql-sql
2010/11/14 Adrian Klaver adrian.kla...@gmail.com:
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
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
|| '';
?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 maps...@gmx.net:
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
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 osvaldo.kuss
Hello
2010/8/30 David Harel harel...@gmail.com:
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
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
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
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
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
Hello
use a LO interface
http://www.postgresql.org/docs/8.4/static/lo-interfaces.html
exact form depends on language that you are use.
Regards
Pavel Stehule
2010/7/5 Trinath Somanchi trinath.soman...@gmail.com:
Hi,
I'm new in using BLOB. How will the insert for storing very large byte
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.
Regards
Pavel Stehule
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
2010/7/5 silly sad s...@bankir.ru:
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
2010/7/5 silly sad s...@bankir.ru:
On 07/05/10 11:03, Pavel Stehule wrote:
2010/7/5 silly sads...@bankir.ru:
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
2010/7/5 silly sad s...@bankir.ru:
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
it in the docs, and I'd like to make
sure I'm not missing something.
it doesn't exists, but it is simple to develop it
CREATE OR REPLACE FUNCTION array_avg(double precision[])
RETURNS double precision AS $$
SELECT avg(v) FROM unnest($1) g(v)
$$ LANGUAGE sql;
Regards
Pavel Stehule
Thanks
Hello
PostgreSQL doesn't support multilangual tables now - etc it isn't
more than one collation per database. But you can store any langual
text when this language is supported by UTF8. Just use UTF8 encoding
for your database.
Regards
Pavel Stehule
see help for initdb and createdb commands
date;
BEGIN
d := CURRENT_DATE;
RETURN d;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 450.665 ms
postgres=# select fo();
fo
2010-05-18
(1 row)
Regards
Pavel Stehule
Another idea would be to: CAST( now() AS DATE )
--
Regards,
Richard Broersma Jr.
Visit the Los
statement
Regards
Pavel Stehule
But I am intrigued by window functions, especially the row_number() and
ntile(int) ones.
Adding row_number() over (order by reverse query) to my query will
return the total number of rows in the first row, letting my deduce the
number of pages remaining, etc
2010/4/20 Pavel Stehule pavel.steh...@gmail.com:
Hello
2010/4/14 Feixiong Li feixion...@gmail.com:
Hi , guys ,
I am newbie for sql, I have a problem when using max() function, I need get
null when there are null in the value list, or return the largest value as
usual, who can do
1 - 100 of 233 matches
Mail list logo
