o idea.
The error is because you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:
SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
> colN_type]* )
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.creda
010-09-03
2 | 2010-09-04
2 | 2010-09-05
2 | 2010-09-06
2 | 2010-09-07
2 | 2010-09-08
(10 rows)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
signature.asc
Description: OpenPGP digital signature
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
Marko Kreen wrote:
On 6/5/07, Brian Mathis <[EMAIL PROTECTED]> wrote:
pgcrypto also supports md5, so I'm not sure what you're referring to
here.
digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
As I already mentioned, *salting* before you hash is a very
important step. I'm not sure if you
Tom Lane wrote:
Richard Jones <[EMAIL PROTECTED]> writes:
I've been profiling a PG database / mix of applications and found that
one statement which takes a very long time to execute is:
PG 8.2 does better with long IN-lists ... although if the list is so
long as to be fetching a significant f
T E Schmitz wrote:
Alvaro Herrera wrote:
T E Schmitz wrote:
I have written a shell script to export data:
psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '
Currently, I am using spaces as field separator but what I really
want is tabs.
How can I specify a TAB character with the -F option?
[EMAIL PROTECTED] wrote:
I'm trying to do a simple query and I'm not sure how to get it to work:
SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
AS A2
FROM test
Problems:
1. All variables are integers. When it does the division, it returns an
integer, but I wan
Tom Lane wrote:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array. A 1-D array of no elements is
'[1:0]
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test=> select array_dims('{}'::integer[]);
Tom Lane wrote:
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output
Markus Bertheau wrote:
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Why would you expect an empty array instead of a NULL? NULL is what
you'd get for other data types -- for example:
regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
?column?
Moran.Michael wrote:
My initial attack plan was to do the following:
1. Call decrypt() with the old-passphrase to decrypt each table's existing
data.
2. Temporarily store the decrypted data in temp tables.
3. Delete all rows of encrypted data from the original tables -- thereby
clearing the table
[EMAIL PROTECTED] wrote:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as "ERROR: function dblink("unknown", "unknown")
does not exist"
Have you installed the dblink functions into your database? See
REA
Muhyiddin A.M Hayat wrote:
How to create Calendar using Function/View.
For example i would like to display date 2004-12-01 to 2004-12-20.
date
--
2004-12-01
2004-12-02
2004-12-03
2004-12-04
2004-12-05
..
..
2004-12-20
-- Use in Postgres 7.4.x and earlie
Sergio Fantinel wrote:
I found how to use, inside a PL/pgSQL function, a two-dimensions array
(matrix).
There is a limitation: the number of the 'columns' of the matrix is
fixed at declaration time (in DECLARE section) and you need to manually
initialize all the elements in the first 'row' of th
Greg Stark wrote:
Just as a side comment, one trick I found very helpful in my mail filters is
to treat any message with one of my message-ids in the references as a
personal message as far as mail notifications. This way I get notifications
for any message on a thread following a post of my own.
I
Markus Bertheau wrote:
Is there a reason the array_in parser accepts additional closing braces
at the end?
oocms=# SELECT '{}}'::text[];
text
--
{}
(1 ÑÑ)
Hmmm, I was *about* to say that this is fixed in cvs (and indeed, the
array_in parser is significantly tightened up compared to previ
Josh Berkus wrote:
This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed
version; crosstab(sourcesql, ncols)) works. If you really need it to be
portable, though, application layer procedural code is likely to be the
easiest and fastest way to go. crosstab just wraps the procedur
Josh Berkus wrote:
The Problem: for each "case" there are from zero to eight "timekeepers"
authorized to work on the "case", out of a pool of 150 "timekeepers". This
data is stored vertically:
authorized_timekeepers:
case_id | timekeeper_id
213447 | 047
132113 | 021
132113 | 115
132
Josh Berkus wrote:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
Are you sure this message isn't coming from some PHP middleware, e.g.
peardb or something. See:
http://us2.php.net/manual/en/function.in-array.php
Joe
---
Bruno Wolff III wrote:
On Sat, Aug 07, 2004 at 09:33:08 +0530,
Kenneth Gonsalves <[EMAIL PROTECTED]> wrote:
any reason why the default reply-to on this list should not be set to the
list? I keep replying to postings only to find later that the reply goes to
the OP and not to the list. reply-all
Josh Berkus wrote:
I'm seeing this bizarre, unreproducable error in my logs:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
I also checked for in_array and it's not a visible built-in function. Is this
maybe a PostgreSQL bug? Version is 7
Theo Galanakis wrote:
How do I explicidly create a cross-database reference?
Mind you these databases are on the same server.
In MSSQL you could do that through enterprise manager.
Any help would be appreciated.
You cannot do cross database references per se, but you can use schemas,
which roughly
Philippe Lang wrote:
But the same query with a parameter returns an error:
select id, usr, code, line1, line2 from tbl, get_lines(code);
--> ERROR: function expression in FROM may not refer to other relations
of same query level
This is as expected and required -- you cannot refer to other FROM
Caleb Simonyi-Gindele wrote:
My problem is that the rows are populated from right to left irrespective of
the column. So the $561.99 in vet1 actually belongs to vet2. There are never
any blank cells in the middle or start of a row - they always come at the
end. How do I get the amount for that date
Devin Whalen wrote:
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',',');
string_to_array
-
{1,2,3
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are som
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are som
Markus Bertheau wrote:
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
Yes, and yes.
regression=# select '{}'::int[];
int4
--
{}
(1 row)
regression=# select NULL::int[];
int4
--
(1 row)
Since NULL array elements are not currently supported, attempting
Torsten Lange wrote:
Hello,
I have a table with measurement values and columns like this:
analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error)
With PL/PgSQL at the end I want try to perform a pivot-like arrangement of
these data:
sample_i
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
How about this:
regression=# select date_trun
Dennis wrote:
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'#
Dennis wrote:
I am trying to return an array from a function and don't seem to be
having luck. The function seems to work fine, but if I do assignment to
an array variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar
Bruce Momjian wrote:
Martin Marques wrote:
Also, for some reason I can't get to sqlstandards.org. Is there any other
place where I can get the SQL200X docs?
Not sure.
See:
http://www.wiscorp.com/sql/sql_2003_standard.zip
Joe
---(end of broadcast)---
Josh Berkus wrote:
4.16.2 Referenceable tables, subtables, and supertables
A table BT whose row type is derived from a structured type ST is
called a typed table. Only a base table or a view can be a typed
table. A typed table has columns corresponding, in name and
decla
Josh Berkus wrote:
6) SQL-99 Distinct Types
7) SQL-99 Structured Types
9) SQL-99 Collection Types
10) SQL-99 Typed tables and views
My answers:
6), 7) Not sure what these are.
Here's the section in SQL99:
4.8 User-defined types
A user-defined type is a schema object, identified by a
Josh Berkus wrote:
BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL?
I saw it, but I've been too swamped to really read it. I'll try to carve
out some time this afternoon.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensi
Josh Berkus wrote:
v_vals TEXT[];
n_vals TEXT[];
try:
v_vals TEXT[] := ''{}'';
n_vals TEXT[] := ''{}'';
You have to initialize the array to something non-null, even if that be
an empty array (note that there is a difference). When trying to append
an element to a NULL valued array, you wind
Achilleus Mantzios wrote:
is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for
usage in an insert statement?
None of base64,hex,escape options in encode() seem to produce
anything close.
This is meant to be used
Tom Lane wrote:
Stephan Szabo <[EMAIL PROTECTED]> writes:
On Tue, 2 Mar 2004, Terence Kearns wrote:
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
As a starting point, SETOF "RECORD" is different from SETOF RECORD given
PostgreSQL's fold c
[EMAIL PROTECTED] wrote:
When I run the function below I recieve an error message saying that
column definitions need to be specified for a function returing a
type RECORD.
I was under the impression that the FOR row IN SELECT... loop would
assign a column structure to the RECORD type. Am I wrong a
Yasir Malik wrote:
[attempts to use a composite type as a field data type of another
composite type]
When I enter that into the command prompt, I the following message:
ERROR: Attribute "street" has composite type street_type
Why is it giving me error message for something I know is true? Also,
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
oops...forget my last reply...I was a bit too
Richard Huxton wrote:
That's not quite the same though, because it means I need to split
ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless
someone is feeling clever this evening.
Would (a series of) partial indexes help?
Joe
---(end of broadca
Rodrigo Sakai wrote:
I insist in my question, is there a way to compile the
plpgsql codes or something like that
no
think about writting this postgres functions in C??
yes
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire
Philippe Lang wrote:
I need to do something similar to a cross tabulation, but without any
aggregation.
See the crosstab() function found in contrib/tablefunc
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister
Tom Lane wrote:
"David" <[EMAIL PROTECTED]> writes:
INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
((54,78), (34,98))};
I think you'd need to double-quote each polygon within the array
literal.
'{"((432,89), (45,87), (89,87))", "..."}'
The array parser doesn't think parens
Bruce Momjian wrote:
Is this a TODO?
Probably -- something like:
Modify array literal representation to handle array index lower bound
of other than one
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http:/
Chris Travers wrote:
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
How 'bout something like this:
CREATE OR REPLACE F
Tom Lane wrote:
Of course; I suppose this bug goes back to Berkeley days. We just
hadn't recognized it before (or at least I hadn't).
Neither had I. But the changes in 7.4 probably make it more likely
people will bump into this as a problem.
Without looking to confirm, I believe SQL99 defines an
Tom Lane wrote:
The other point about pg_dump failing to correctly restore arrays with
nondefault lower bounds is a good one, though. We need to think about
how to fix that.
I'll put some thought into it, but note that it is hardly a new issue --
it's been possible to create an array with < 1 low
Tom Lane wrote:
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
can anyone explain why
SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
Because array_prepend keeps the subscripts of the existing array
elements the same. This was discussed during development of the
code, but I
Kumar wrote:
select * from .fn_email(1) as (email_folder_id int4,email_folder_name
varchar,descrip varchar,msgcount int8,unreadcount int8,size int8);
Is it possible to fetch only one column (the 'msgcount') from the
function. Because I am interested in SUM(msgcount). Please shed some
light.
What
Chris Bowlby wrote:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
This is the "classic" SRF error -- you need to use an SRF like a
relation in the FROM clause, so do this instead:
select * FROM get_account_info('
Michael Fuhr wrote:
I wrote a haversine() function that uses the Haversine Formula to
calculate the great circle distance between two points on a sphere
(assuming the earth is a perfect sphere is accurate enough for my uses).
Here's a web site with related info:
http://www.census.gov/cgi-bin/geo/gi
ow wrote:
My impression was that the index "I_bca" covers the query, hence there should
not be a need to go to the table itself. Why would it?
Postgres always has to go to the table. The ability to read data
directly from indexes (ala other RDBMSs) has been discussed, but not
implemented. IIRC it
jasiek wrote:
On Sat, 29 Nov 2003 08:49:24 -0800 (PST), ow wrote
explain select b, c, a
from test
group by b, c, a
having count(*) > 1
Why would you expect this to use an index scan when it needs to read the
entire table? If you read the whole table (or even a significant
fraction of it), a seq
Richard Huxton wrote:
On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
I want to write a function of the following type
CREATE FUNCTION test ( )
RETURNS setof MyTable
AS
'SELECT * FROM MyTable WHERE id IN $1'
LANGUAGE 'SQL' ;
Not as you've done it. You c
Andreas Tille wrote:
test=# select PLpgSQLDepartmentSalaries() ;
This should be:
regression=# select * from PLpgSQLDepartmentSalaries();
departmentid | totalsalary
--+-
1 | 8
2 | 6
(2 rows)
HTH,
Joe
---
Kumar wrote:
I am migrating an SQL Server 2000 database to Postgres 7.3.4 running
on RH Linux 7.2. While migrating I encounter SQL Server REVERSE( )
function, seems no such functions at Postgres.
Is there a equivalent function available at Postgres? Please shed
some light
How about:
create or repl
Abdul Wahab Dahalan wrote:
If I've a table like below.
kk kjpngkvote
01 02 c 10
01 02 b 5
How do I make a query so that I can get a result
like this?
kk kjpngkvote
01 02c,b 15
create or replace function accum_text(tex
BenLaKnet wrote:
I use postgresql 7.2.3
How can I use connectby ??
Must I install files ? or packages ? or it is recommanded to upgrade
dataserver ?
You need to upgrade. Either install 7.3.4 or wait a few weeks and
install 7.4 when it is released.
Joe
---(end of broadc
David B wrote:
SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on",
r_app.joined_on ; -- HOW do I reference this value?...this does not work
Try either making that first line:
... AS joined_on,
(i.e. without the double quotes) or make the second one:
r_app."joined_on";
(i.
Adam Witney wrote:
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Yeah, that's a limitation of the version of crosstab distributed with
Josh Berkus wrote:
Yes, I believe so (well, actually the optimizer). An inlined SQL
function ends up behaving like a macro that expands at run time and is
therefore quite fast -- no function call overhead at all.
... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented.
Yeah, tha
Richard Huxton wrote:
On Monday 20 October 2003 18:24, Joe Conway wrote:
This question gets even more complex in 7.4, where many simple SQL
functions will get inlined, and library preloading is available to speed
that first PL/pgSQL call.
What will be the effects of inlining? Does it mean the
Richard Huxton wrote:
So - gain by not re-planning on every call, but maybe lose because your plan
is not so precise.
Of course, any queries you build dynamically and run via EXECUTE will have to
be planned each time.
This question gets even more complex in 7.4, where many simple SQL
functions
Thomas Wegner wrote:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
Please see the documentation (README.tablefunc).
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
Also, this will run faster if you do it as a SQL function:
CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;
This is definitely the solution I'd recommend for 7.4 (because 7.4 wou
Richard Sydney-Smith wrote:
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS
int4 AS ' -- search for the position of $2 in $1
declare srcstr alias for $1; searchstr alias for $2;
begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql'
VOLATILE;
You are missing the "end"
Merrall, Graeme wrote:
Am I right in thinking that recursive procedures and procs returning row
sets would allow us to better emulate this behaviour? As anyone looked
at it yet?
See connectby() in contrib/tablefunc. Someone was working on SQL99
recursive queries but it didn't get done for 7.4 -- p
Chris Faulkner wrote:
I would like to do it like this in Oracle
select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0
So if a row only has two elements in the array, but the first two both had
values "0"
Chris Faulkner wrote:
Is there a function in postgres to return the length of an array field ? I
have seen array_dims(array) but this returns a character value. Ideally, I'd
like something numeric returned.
Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do
this (for a one-dim
Kumar wrote:
Create table t1 (c1 int, c2 varchar, c3 varchar);
Create or Replace function sel_t1 () returns setof records as '
select c1, c2, c3 from t1; ' Language SQL;
It was fine and created a function. while i execute it as
select sel_t1;
I got the following error.
ERROR: Cannot display
George McQuade wrote:
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
Well this is a fairly complex
George McQuade wrote:
date tran glamt
08/20/03 1001 3010 -30.00
08/20/03 1001 1030 -300.00
08/20/03 1001 1060 +330.00
08/20/03 1002 ...next transaction
...
and I need to convert to:
date glcr gldb amt
08/20/03 1060 3010 30.00
08/20/03 1060 1030 300.00
in other words, the negative
Mike Winter wrote:
On Wed, 20 Aug 2003, Rod Taylor wrote:
Ensure your IN list is unique. You might find better times by through
an indexed temp table.
That is what I ended up doing, but it's not a very elegant solution.
MySQL does queries of this type orders of magnitudes faster than Postgres
on
Bertrand Petit wrote:
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FR
Scott Cain wrote:
Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is
109M.
Thanks. I'll grab a copy from home later today and see if I can find
some time to poke at it.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscr
Scott Cain wrote:
On Mon, 2003-08-04 at 11:53, Tom Lane wrote:
I find it really, really hard to believe that a crude reimplementation
in plpgsql of the TOAST concept could beat the built-in implementation
at all, let alone beat it by two orders of magnitude.
Either there's something unrealistic abo
Mark Bronnimann wrote:
I was hoping to eliminate the parse call on the view because I was doing
the where clause on the view instead of putting the where in the view.
In all, I was hoping to keep a single view called from multiple functions
with different where clauses. Yep... I shoulda known
Cristian Cappo A. wrote:
Tried, but...
>> select (foo(10::int2,20::int2))[1];
>> ERROR: parser: parse error at or near "[" at character 32
I'm using the version 7.3.3
Sorry, it works on 7.4devel, so I thought it might on 7.3 as well. In
any case, this works on 7.3.3:
test=# select f1[1] fro
Cristian Cappo wrote:
>>> select __function(10::int2, 20::int2)[1]
^^^ parsing error.
Try:
create or replace function foo(int2, int2 ) returns _varchar as '
select ''{1,2}''::_varchar
' language 'sql';
regression=# select (foo(10::int2, 20::int2))[1];
foo
-
1
(1 row)
Mike Rylander wrote:
Thank you! This is great news. Is there a projected release date for 7.4?
Not exactly an officially projected date, but in the past IIRC beta/RC
has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October.
Also, is there a published roadmap, or should I just
Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view
(for some definition of simplify). The current idea I have is to shovel
values from multiple rows in one table into an array in the view. The tables
look something like this:
Is anything like
BenLaKnet wrote:
I see connect by in Oracle
??? is there an equivalent in PostgreSQL or not ??
Someone is working on the SQL99 equivalent, but it isn't done yet.
Perhaps for 7.5.
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
David Olbersen wrote:
Now the question: is there a single index I can create that will be
used when my WHERE clause contains either urlhost or urltld? I could
create two functional indexes, but that seems a bit silly to me.
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW,
Steve Wampler wrote:
I've got a simple nested query:
select * from attributes where id in (select id from
attributes where (name='obsid') and (value='oid00066'));
that performs abysmally. I've heard this described as the
'classic WHERE IN' problem.
I may be missing something, but why can't
Benoît Bournon wrote:
I have to make a function that returns a tree with title and link of a
table.
Recursively, a information depends on a parent information.
It is to organise a menu with parent dependance.
How is it possible and faster ? in C ? pl/pgsql or other ?
On 7.3 and later: see con
Luis Sousa wrote:
How can I set a function as setuid ?
I take a look at the documetation, on Reference Manual and the only
reference I saw to it was on SET SESSION AUTHORIZATION.
See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html
CREATE [ OR REPLACE ] FUNC
Richard Rowell wrote:
Can I accomplish this with postgresql without involving an external
process (like say perl)? I guess I could put the upgrade stuff into
PL/SQL functions and just drop the functions when I'm done, but I was
hoping for something a little "cleaner".
There is no way (currently at
Ang Chin Han wrote:
Tom Lane wrote:
create function greatest(anyelement, anyelement) returns anyelement as
'select case when $1>$2 then $1 else $2 end' language sql;
Any chance of this making it into 7.4's contrib? Maybe with enough
documentation to make it a tutorial for PostgreSQL's user functi
Alon Noy wrote:
From what I tried it is possible to create such a function but it is not
possible to call it ?!
Can anyone provide an example?
create table foo (f1 int, f2 text);
insert into foo values(1,'a');
insert into foo values(2,'b');
insert into foo values(3,'c');
create or replace function
Josh Berkus wrote:
create or replace function greatest(anyelement, anyelement) returns
anyelement as 'select case when $1 > $2 then $1 else $2 end' language
'sql';
Way cool. I'd have to imagine that it would blow up if you did this, though:
select greatest ( 512, now() );
With an "Operator is no
Joe Conway wrote:
In 7.4devel (just starting beta) you can do this:
Actually to correct myself, we just started "feature freeze" for 7.4,
with beta planned to start on or about July 15th.
Sorry for any confusion caused.
Joe
---(end of
Greg Stark wrote:
SELECT greatest(a,b) FROM bar
would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.
You could define your own functions to do this but it would be tiresome to
define one for every datatype.
In 7.4devel (just starting
Radu-Adrian Popescu wrote:
.
begin
_res.code:=1;
select id into iid from log where id=_id;
if not found then begin
_res.msg:=''insert'';
*insert into log (log, data) values (_log, _data);
if not found* then begin
_res.msg:=_res.msg
Mr Weinbach, Larry wrote:
But at execution time I am getting thi error :
WARNING: Error occurred while executing PL/pgSQL
function word_case
WARNING: line 5 at return next
ERROR: Set-valued function called in context that
cannot accept a set
You didn't show the execution time SQL statement, but
Michael A Nachbaur wrote:
Source Target
[EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],
It isn't clear from your description what you want (to me, anyway), but
I'd guess something like this:
regression=# select * from src2tgt;
source| target
-+
Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or tab
1 - 100 of 156 matches
Mail list logo
