Re: [SQL] Returning a set from an function
Keith: This is the general approach I use over and over and over -- This is a PLPGSQL function that returns a SETOF tablename%ROWTYPE If you need the full schema and table and everything that goes with this -- let me know --- CREATE OR REPLACE FUNCTION sys_aclsubmenu(int4) RETURNS SETOF sys_tree_components AS $BODY$ DECLARE MNU ALIAS FOR $1; DECLARE OUT sys_tree_components%ROWTYPE; DECLARE CHILD sys_tree_components%ROWTYPE; BEGIN RAISE NOTICE 'sys_aclsubmenu(integer, varchar) called for item: %', $1; FOR OUT IN SELECT * FROM sys_tree_components WHERE parent = $1 AND active_flag LOOP IF (OUT.id != OUT.parent) THEN FOR CHILD IN SELECT * FROM sys_aclsubmenu(OUT.id) LOOP RETURN NEXT CHILD; END LOOP; END IF; RETURN NEXT OUT; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM sys_aclsubmenu(1) ORDER BY parent, id ; ""Keith Hutchison"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > G'day, > > Looking for an example showing how to return a set from either a sql > function or a plpsqq function. > > Thanks > > -- > Keith Hutchison > http://balance-infosystems.com http://realopen.org > http://www.kasamba.com/Keith-Hutchison > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] how to use recursion to find end nodes of a tree
Hello All, I have been having a really hard time trying to come up with a pl/pgsql recursive function to returns the end nodes of a tree. Here is an example table definition: CREATE TABLE parent_child ( parent_id integer NOT NULL, child_id integer NOT NULL ); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4); INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5); INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9); INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10); This produces the following tree of data: 1 ___|___ | | | 2 3 4 _|_ _|_ | | | | | 5 6 7 8 9 | 10 I want to create a function that returns the terminating nodes of of this tree below a certain level i.e. if I input 1 to the function I need it to return 5,6,3,7,8,10. If I input 4 to the function I would get 7,8,10. I have written recursive functions which return all nodes on a branch of a tree but I can't think of a way to return the end nodes does anyone know of a solution? Many thanks, Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to use recursion to find end nodes of a tree
Hello All,
I have been having a really hard time trying to come up with a pl/pgsql
recursive function to returns the end nodes of a tree.
Here is an example table definition:
CREATE TABLE parent_child (
parent_id integer NOT NULL,
child_id integer NOT NULL
);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3);
INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4);
INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5);
INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8);
INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9);
INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10);
This produces the following tree of data:
1
___|___
| | |
2 3 4
_|_ _|_
| | | | |
5 6 7 8 9
|
10
I want to create a function that returns the terminating nodes of
of this tree below a certain level i.e. if I input 1 to the function
I need it to return 5,6,3,7,8,10. If I input 4 to the function I would
get 7,8,10. I have written recursive functions which return all nodes
on a branch of a tree but I can't think of a way to return the end nodes
does anyone know of a solution?
I haven't programmed in PL/pgSQL in a while, but I'll write some pseudo
code. I think the code should be similar:
func(int node)
{
dynamic_array s;
dynamic_array leaves;
int top, count, leaf_id, popped, child;
leaf_id = top = 0;
s[top] = node;
count = 1;
// to a depth first search
while(count != 0)
{
popped = s[top];
top--;
count--;
foreach(select pc.child_id into child from parent_child pc where
pc.parent_id = popped)
{
select * from parect_child pc where parent_id = child;
// a count of zero indicates that child node has no children
if(count_of_above_query = 0)
{
leaves[leaf_id] = child;
leaf_id++;
}
else
{
// not a leaf, so add it to the stack for the next time through
// the loop
top++;
s[top] = child;
count++;
}
}
}
return leaves;
}
Regards,
Yasir
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Hi, Eugene, Eugene E. wrote: > he did not request this representation. it is _by_default_ He used a function that provided it by default. He could use the other function that allows him to select which representation he wants. > if you wish to provide it by request, please do it. I cannot provide anything, because I'm not a PostgreSQL developer. To be honest, I can provide the user with nice Java Objects, because I'm the maintainer of the PostGIS Java extension, but that's all. >> A user that does not have a need in C-Strings can fetch the binary >> representation, getting higher efficency for all datatypes. > and lose the pretty good representation of all other columns in the same > request. It is not "pretty good". It is human readable, but it are C-Strings with some text. It uses much more CPU power on both sides, Date Formats can be misleading, and PostGIS even reverted to a binary like format for their canonical text representation because the users complained about coordinate drift due to rounding errors in the floating point input/output routines, so pg_dump and restoring the database changed the data. So I cannot see that the textual from is superior for _any_ of the datatypes. IMHO, its sole purpose is to have a generic way to represent the data for generic tools as pg_dump -F p, pgadmin3, psql and such, which cannot know the (possibly user-defined) datatypes in advance. >> There is no philosophy but orthogonality. >> There's a textual and a binary form of datatypes. For varchar, byta, >> int4, float, PostGIS geometries etc... > good. i ask you to slightly change "textual" representation of bytea. This will achieve at least the following: - It will break all libraries, tools and applications that rely on the current behaviour. - It will break the guarantee for generic applications that the text representation of every datatype can be handled as text. - It will break pg_dump -F p (which is the default for pg_dump by the way), thus making it _impossible_ to have "plaintext" dumps of a database, with no easy way of reinventing this behaviour. Those dumps are to be edited with text editors, which don't cope easily with nullbytes and other waste... >> The text representation is pretty useful for human readers for _most_ >> datatypes, the binary representation is much easier to parse for >> programs. > You are right. > but > Who said that i can not display something ? > i thougth, human-readability of some data depends completely on how > CLIENT-SIDE interpret it. > server do not know and should not know > what data is human readable or printable... etc. So what you say is that the canonical text representation should be abandoned completely. Fine. The problem is that all generic applications, that don't know about the concrete datatypes, will get impossible. psql, pg_dump, pgadmin and others. Different from "normal" applications which can have their specific datatypes hardwired in the code or whatever, those applications cannot be taught about how to present the data to an user in a generic way, if there's nothing in the backend. Users and extensions can invent new datatypes as they want, how do you expect the authors of pgadmin or psql to cope with proprietary in-house datatypes of a certain PostgreSQL user? >> So use the binary representation for everything if you don't want to >> display the data to the user directly. > The problem we discuss is not about displaing or printig at all. > Some applications want "textual-form" -- most applications > but not only to display > and in the _same_ query the same applications want bytea... Why do you try so hard to resist understanding the whole point? Those applications _get_ bytea. They just get a Cstring-safe representation of it. It's just like you have to put "quotes" around and \escapes into a string in your program sources if you use any of the weird characters. You have the decision between text and binary format for your query. As libpq is a low level API, it does not abstract you from this difference. You can you use a higher level API that abstracts over the whole issue and gives you nice Objects (like the jdbc library), then you don't have to cope with those representations at all. It also may make sense to provide an extension for lipq that lets you select binary and textual representation column-wise (which might need a protocol extension, I don't have the specs in mind). But it absolutely does not make any sense to break the whole concept of text representations by making it binary for a single datatype. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] concatenation with a null column (using ||) nulls the result?
inventory=> SELECT cabinets_name, cabinets_description FROM cabinets WHERE cabinets_datacenters = 2; cabinets_name | cabinets_description ---+-- 548-4th-Cab2 | 548-4th-RR1 | 548-4th-RR2 | 548-4th-Cab1 | (4 rows) inventory=> SELECT cabinets_name || ' - ' || cabinets_description AS concat FROM cabinets WHERE cabinets_datacenters = 2; concat - 548-4th-Cab1 - (4 rows) Note: The cabinets_description for the "548-4th-Cab1" row is " ", not NULL, hence it being displayed. Is this standard SQL behavior? Client is from rpm: postgresql-8.0.7-1.FC4.1 Server is from rpm: postgresql-server-8.0.7-1.FC4.1 -neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] concatenation with a null column (using ||) nulls the result?
Neil Harkins <[EMAIL PROTECTED]> writes: > Is this standard SQL behavior? Yes. SQL92 6.13 , general rule 2: 2) If is specified, then let S1 and S2 be the re- sult of the and , respectively. a) If either S1 or S2 is the null value, then the result of the is the null value. ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] concatenation with a null column (using ||) nulls the result?
Neil Harkins wrote: > Note: The cabinets_description for the "548-4th-Cab1" row is " ", > not NULL, hence it being displayed. Is this standard SQL behavior? Yes; something || NULL yields NULL. If you want NULL to behave as "" for the purpose of the concatenation, try SELECT cabinets_name || ' - ' || COALESCE(cabinets_description, '') AS concat FROM cabinets WHERE cabinets_datacenters = 2; I'm assuming cabinets_name is NOT NULL, so it doesn't need COALESCE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] concatenation with a null column (using ||) nulls the result?
On Monday 10 April 2006 05:55 pm, Alvaro Herrera saith: > Neil Harkins wrote: > > Note: The cabinets_description for the "548-4th-Cab1" row is " ", > > not NULL, hence it being displayed. Is this standard SQL behavior? > > Yes; something || NULL yields NULL. If you want NULL to behave as "" > for the purpose of the concatenation, try > > SELECT cabinets_name || ' - ' || COALESCE(cabinets_description, '') AS > concat FROM cabinets WHERE cabinets_datacenters = 2; > > I'm assuming cabinets_name is NOT NULL, so it doesn't need COALESCE. > > -- Good to know. Thanks for the input... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] concatenation with a null column (using ||) nulls the
On Mon, 2006-04-10 at 14:36 -0700, Neil Harkins wrote: > inventory=> SELECT cabinets_name, cabinets_description > FROM cabinets WHERE cabinets_datacenters = 2; > cabinets_name | cabinets_description > ---+-- > 548-4th-Cab2 | > 548-4th-RR1 | > 548-4th-RR2 | > 548-4th-Cab1 | > (4 rows) > > inventory=> SELECT cabinets_name || ' - ' || cabinets_description AS concat > FROM cabinets WHERE cabinets_datacenters = 2; > concat > - > > > > 548-4th-Cab1 - > (4 rows) > > Note: The cabinets_description for the "548-4th-Cab1" row is " ", > not NULL, hence it being displayed. Is this standard SQL behavior? > I don't know if this is the "correct SQL" answer, however, in the past, I've used the COALESCE() function to handle this situation. COALESCE(value [, ...]) The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display, for example: SELECT COALESCE(description, short_description, '(none)') ... Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. Regards. > Client is from rpm: postgresql-8.0.7-1.FC4.1 > Server is from rpm: postgresql-server-8.0.7-1.FC4.1 > > -neil > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: 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] how to use recursion to find end nodes of a tree
On Mon, 2006-04-10 at 16:09 +0100, [EMAIL PROTECTED] wrote: > Hello All, > > I have been having a really hard time trying to come up with a pl/pgsql > recursive function to returns the end nodes of a tree. > Here is an example table definition: > > CREATE TABLE parent_child ( > parent_id integer NOT NULL, > child_id integer NOT NULL > ); > > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3); > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4); > INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5); > INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9); > INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10); > What you appear to have is really this, with a missing first node: CREATE TABLE parent_child ( parent_id integer NOT NULL, this_node_id integer NULL, ); INSERT INTO parent_child (parent_id, this_node_id) VALUES (0, 1); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 2); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 3); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 4); INSERT INTO parent_child (parent_id, this_node_id) VALUES (2, 5); INSERT INTO parent_child (parent_id, this_node_id) VALUES (2, 6); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 7); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 8); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 9); INSERT INTO parent_child (parent_id, this_node_id) VALUES (9, 10); This makes it easy to search from leaf to root, but not from root to leaf. Without a list of child_ids in each node you must search the whole table for nodes that have the current node id as their parent_id in order to determine if a node is a leaf node or not. Perhaps you can include a child_id[] in each node, or a has_children boolean flag that you set and unset when inserting or deleting rows. But perhaps you can get PostgreSQL to do it for you by setting this_node_id as primary key and parent_id as foreign key referencing this same table. You could then test if it's a leaf node by attempting to change the node's this_node_id to some out-of-range value and see if it produces as error. If no error then it's a leaf node, (then you must restore this_node_id - I would try just negating it for the test, so I don't have to actually store the original value somewhere). > This produces the following tree of data: > >1 > ___|___ >| | | >2 3 4 > _|_ _|_ > | | | | | > 5 6 7 8 9 > | > 10 > > I want to create a function that returns the terminating nodes of > of this tree below a certain level i.e. if I input 1 to the function > I need it to return 5,6,3,7,8,10. If I input 4 to the function I would > get 7,8,10. I have written recursive functions which return all nodes > on a branch of a tree but I can't think of a way to return the end nodes > does anyone know of a solution? > > Many thanks, > > Mike > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] global variables in plpgsql?
Folks, In v8.0.3 documentation, plperl, plpython and pltcl allow declaration of variables that are global and persistent within a session. begin_quote 37.4. Global Values in PL/Perl You can use the global hash %_SHARED to store data, including code references, between function calls for the lifetime of the current session. end_quote So, a couple of questions 1) Can you declare global values from plpgsql? 2) If so, is there a way of avoiding namespace pollution? (perhaps the equivalent to Oracle's use of plsql package variables) tia -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] GROUP BY issue
Hy list. I have 3 tables: performance event pool_performance each event has it's performances, and can be assigned to one or more pools. pool_performance holds performance_id, pool_id and from_date performance has event_id and performance_id as primary key what I want to do is select min(pool_performance.from_date), pool_id, event_id group by performance_id but done in a way that I group only performances belonging to the same event but for the same pool, and not all performances. Thanks in advance, Jure ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] slow 'IN' clause
I have a slow sql: SELECT * FROM mytable WHERE id IN (1,3,5,7,3k here...); mytable is about 10k rows. if don't use the "IN" clause, it will cost 0,11 second, otherwise it will cost 2.x second I guess pg use linear search to deal with IN clause, is there any way to let pg use other search method with IN clause? (ex.Binary Search or hash Search) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Special meaning of NL string
Title: Message Hello, I wonder if there is an issue with the string 'NL'. In my application NL stands for Netherlands and is thus a country code abreviation. The query SELECT COUNT(*) FROM Table WHERE Field1 = 'NL' OR Field2 = 'NL' does a sequence scan instead of an index scan, and is thus very slow. If I replace NL by BE (Belgium) the query does an index scan. If I replace OR by AND it also does an index scan. So apparently the string NL in the OR Where clause decides the query planner not to execute an index scan. The database encoding schema is SQL_ASCII. Thanks, Fred Vellinga
Re: [SQL] [GENERAL] pgcrypto-crypt
AKHILESH GUPTA wrote:
how do we compare the existing data in the table with the entered one?
same way as anything else, for example:
select * from users where passwd=md5('my_password');
is there any other function which we can use here for both cases
encryption as well as for decryption at the script as well as database
level
why do you need it encrypted?
Please do reply-all - you will get a quicker response (from me and the
list(s) might have suggestions I don't).
On 4/6/06, *chris smith* <[EMAIL PROTECTED] >
wrote:
On 4/6/06, AKHILESH GUPTA <[EMAIL PROTECTED]
> wrote:
> dear all,
> i want to encrypt and decrypt one of the fields in my table
(i.e-password
> field)
> i have searched and with the help of pgcrypto package, using
function
> "crypt", i am able to encrypt my data,
> but there is nothing which i found to decrypt that same data,
> plz anybody give me the function to decrypt that encrypted
value.
The crypt function can't be decrypted (whether it's in postgresql or
anywhere else).
Crypt is meant to be used for passwords and such that you don't need
to reverse (you only compare against).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)
GSM:-(+919891606064)
(+911744293789)
"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
--
Postgresql & php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 1: 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
[SQL] Joins involving functions
I have a function that returns a row from a table, e.g.: create table foo (a integer); create function f_foo (integer) returns foo language 'sql' as $foo$ select * from foo where a = $1 limit 1 $foo$ create table bar (b integer); select * from bar cross join f_foo(bar.b) as foo; Unfortunately, this last statement returns an error: NOTICE: adding missing FROM-clause entry for table "bar" ERROR: function expression in FROM may not refer to other relations of same query level Is there a method for joining function calls to tables? The only other approach I can think of is to write another function that returns "setof". -- Jeffery Boes <>< [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] pgcrypto-crypt
On 4/6/06, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote: > dear all, > i want to encrypt and decrypt one of the fields in my table (i.e-password > field) > i have searched and with the help of pgcrypto package, using function > "crypt", i am able to encrypt my data, > but there is nothing which i found to decrypt that same data, > plz anybody give me the function to decrypt that encrypted value. The crypt function can't be decrypted (whether it's in postgresql or anywhere else). Crypt is meant to be used for passwords and such that you don't need to reverse (you only compare against). -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Special meaning of NL string
"Vellinga, Fred" <[EMAIL PROTECTED]> writes: > The query > SELECT COUNT(*) FROM Table WHERE Field1 = 'NL' OR Field2 = 'NL' > does a sequence scan instead of an index scan, and is thus very slow. If I > replace NL by BE (Belgium) the query does an index scan. Probably, 'NL' is a lot more common than 'BE' in your table ... the planner does examine statistics while deciding what sort of scan to use. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] global variables in plpgsql?
On Apr 10, 2006, at 9:17 PM, [EMAIL PROTECTED] wrote: So, a couple of questions 1) Can you declare global values from plpgsql? 2) If so, is there a way of avoiding namespace pollution? (perhaps the equivalent to Oracle's use of plsql package variables) plpgsql does not have global variables. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
