[SQL] Equivalent of Reverse() functions
Dear Friends, 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 Regards Kumar
Re: [SQL] Equivalent of Reverse() functions
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 replace function reverse(text) returns text as '
return reverse($_[0]);
' language plperl;
regression=# select reverse('abcdef');
reverse
-
fedcba
(1 row)
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Unsigned numbers
10x you all ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Scaler forms as function arguments
On Wed, 26 Nov 2003, Richard Huxton wrote: > Not as you've done it. You could pass in text "(1,2,3)", build your query and > use EXECUTE to execute it. This boils down the question to the problem which occured with your promissing link below, because I need to use PL/pgSQL, right? > Alternatively, you might be able to do it with an > array parameter (sorry, I don't use arrays, so I can't be sure). I'll give that a try. > Read the section on plpgsql in the manuals, you return results one at a time. > For some examples, see http://techdocs.postgresql.org/ and look for the "Set > Returning Functions" item. A very interesting article but if I try the example code: create table department(id int primary key, name text); create table employee(id int primary key, name text, salary int, departmentid int references department); insert into department values (1, 'Management'); insert into department values (2, 'IT'); insert into employee values (1, 'John Smith', 3, 1); insert into employee values (2, 'Jane Doe', 5, 1); insert into employee values (3, 'Jack Jackson', 6, 2); create function GetEmployees() returns setof employee as 'select * from employee;' language 'sql'; create type holder as (departmentid int, totalsalary int8); create function SqlDepartmentSalaries() returns setof holder as ' select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid ' language 'sql'; create or replace function PLpgSQLDepartmentSalaries() returns setof holder as ' declare r holder%rowtype; begin for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop return next r; end loop; return; end ' language 'plpgsql'; I get: test=# select PLpgSQLDepartmentSalaries() ; WARNING: Error occurred while executing PL/pgSQL function plpgsqldepartmentsalaries WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set test=# Any hint what might be wrong here? I'm using PostgreSQL 7.3.2 under Debian GNU/Linux (testing). Kind regards Andreas. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problem: Postgresql not starting
> > Hi All > I am using postgresql 7.2 on Linux. It does not start when i am trying th= > is > /etc/rc.d/init.d/postgresql restart. > It give an error saying "postmaster already running". > I have done > rm -f /var/lib/pgsql/data/postmaster.pid > and > rm -f /var/run/postmaster.pid > > But it still says the same. whenever i restart with the above command > When say "stop" it says failed and when i say "start" it says postmaster > already running. > Please help me out > > --m > I've seen the other replies, but IMHO you are not supposed to remove /var/lib/pgsql/data/postmaster.pid to stop the postmaster but kill $(head -n 1 $PGDATA/postmaster.pid) RTFM HTH Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Validity check in to_date?
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
2007-01-03
to_timestamp() seems to work the same way. It's probably useful sometimes,
but not in my case... Is it how it supposed to work?
If so, how can I do such a validity check?
If not, has something changed in 7.4?
In any case, I have to find a workaround now and will appreciate any help.
fduch=# SELECT version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Scaler forms as function arguments
Andreas Tille wrote: test=# select PLpgSQLDepartmentSalaries() ; This should be: regression=# select * from PLpgSQLDepartmentSalaries(); departmentid | totalsalary --+- 1 | 8 2 | 6 (2 rows) HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scaler forms as function arguments
On Thu, 27 Nov 2003, Joe Conway wrote: > Andreas Tille wrote: > > test=# select PLpgSQLDepartmentSalaries() ; > > This should be: > regression=# select * from PLpgSQLDepartmentSalaries(); > departmentid | totalsalary > --+- > 1 | 8 > 2 | 6 > (2 rows) Well, it is easy to understand what it 'should be' reading the code - but it throws the error message I posted. Is this possibly a feature of a higher PostgreSQL version than 7.3.2? Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] explicit joins wrong planning
Hi
SELECT version();
PostgreSQL 7.3.4 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.2 20031005 (Debian prerelease)
Let's say I have 3 tables:
groups (
groupid integer primary key,
namevarchar,
begindate date
);
offsets (
offset_id integer,
groupid integer references groups,
offset_value integer
);
events (
offset_id integer references offsets,
event_datedate,
primary key (offset_id,event_date)
);
explain analyze select *
from
groups g
join offsets o using (groupid)
join events e on (e.offsetid=o.offset_id and
e.event_date=g.begindate+o.offset_value)
where g.name='some_name';
Postgres doesn't use join on these both fields and doesn't use index
scan properly.
I get:
Hash Cond: ("outer".offset_id = "inner".offset_id)
Join Filter: ("outer".event_date = ("inner".begindate +
"inner".offset_value))
Why?
I lost few hours trying to fix it and I found, that copying one of these
conditions into where clause solved my problem:
explain analyze select *
from
groups g
join offsets o using (groupid)
join events e on (e.offsetid=o.offset_id and
e.event_date=g.begindate+o.offset_value)
where g.name='some_name' and e.offsetid=o.offset_id;
Join Filter: ("outer".event_date = ("inner".begindate +
"inner".offset_value))
Nested Loop...
Join Filter: ("outer".offset_id = "inner".offset_id)
Why? What was I doing wrong?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Scaler forms as function arguments
On Thursday 27 November 2003 16:40, Andreas Tille wrote: > On Thu, 27 Nov 2003, Joe Conway wrote: > > Andreas Tille wrote: > > > test=# select PLpgSQLDepartmentSalaries() ; > > > > This should be: > > regression=# select * from PLpgSQLDepartmentSalaries(); > > departmentid | totalsalary > > --+- > > 1 | 8 > > 2 | 6 > > (2 rows) > > Well, it is easy to understand what it 'should be' reading the code - but > it throws the error message I posted. Is this possibly a feature of a > higher PostgreSQL version than 7.3.2? No - look carefully at Joe's response. He's calling it like: SELECT * FROM my_function(); You treat the function like a table. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Scaler forms as function arguments
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 could pass in text "(1,2,3)", build your query and use EXECUTE to execute it. Alternatively, you might be able to do it with an array parameter (sorry, I don't use arrays, so I can't be sure). In 7.4 you could use an array. It would look like this: CREATE TABLE mytable (id int, idval text); INSERT INTO mytable VALUES (1,'a'); INSERT INTO mytable VALUES (2,'b'); INSERT INTO mytable VALUES (3,'c'); CREATE FUNCTION test (int[]) RETURNS setof MyTable AS ' SELECT * FROM mytable WHERE id = ANY ($1) ' LANGUAGE 'SQL' ; regression=# SELECT * FROM test(ARRAY[1,3]); id | idval +--- 1 | a 3 | c (2 rows) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
