A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.

learn more… | top users | synonyms

2
votes
1answer
35 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
1
vote
1answer
309 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
0
votes
1answer
21 views

Mysql functions for web apps

I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress. Mostly this is for a few extensions I want to do for Magento. The ...
0
votes
1answer
37 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
0
votes
1answer
40 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
3
votes
0answers
138 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
1
vote
0answers
107 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
0answers
112 views

Inline TVF with One Row Result

I have several times converted scalar functions into inline tvf. Usually this is the basic design I used in inline tvf: CREATE FUNCTION TVF(@ID INT) RETURNS TABLE AS RETURN( WITH BASE AS( ...
1
vote
0answers
279 views

Calling a function in remote database inside a stored procedure

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I ...
1
vote
0answers
810 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
0
votes
0answers
6 views

Trying to set up automatic process to append and update tuples

I am trying to figure out a way to automate a process that I currently have that appends a new column on and updates two or three other columns when I add a new table into a database. The goal is to ...
0
votes
0answers
32 views

Wrapper function with SECURITY DEFINER is slow

I have a strange behavior of wrapper function with SECURITY DEFINER option. Function 1 - Queries data what is not directly accessible by user. Function 2 - Is a wrapper that calls function 1 with no ...
0
votes
0answers
21 views

Function performance issue

This one is puzzling me since yesterday and maybe someone could shed some light on this. We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
0
votes
0answers
70 views

Informix error 674: Routine (gc_init) can not be resolved.

I have created function in Informix like this. Now I want to drop that function. I have successfully dropped auxiliary functions e.g. DROP FUNCTION gc_init(VARCHAR(255)) When I try to drop ...
0
votes
0answers
20 views

Convert this Ruby function into MySQL Function?

From my answer at SO, I have the following function: def rgb_to_hue(r,g,b) # normalize r, g and b r_ = r / 255.0 g_ = g / 255.0 b_ = b / 255.0 c_min = [r_,g_,b_].min c_max = ...
0
votes
0answers
53 views

Postgresql function taking long time on newly restored database

I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute. Now I have restored a new PostgreSQL 9.0 dump using following command: pg_restore -hx.x.x.x -p5432 -d ...