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

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 ...
2
votes
1answer
190 views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function on that cross tab, so I can pass a date, and get data for the specific date? Also is it a good practice to ...
1
vote
3answers
31 views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
1
vote
1answer
76 views

Use NOLOCK hint when calling table valued function

Suppose the following function: CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr My actual function will select actual data from many different tables. I (mostly) understand ...
0
votes
1answer
20 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
33 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 ...
7
votes
5answers
508 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
0
votes
1answer
33 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
1
vote
2answers
66 views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
2
votes
2answers
70 views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
0
votes
2answers
95 views

Remove a period if it is the first character in a varchar column

I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first ...
0
votes
0answers
56 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
1answer
29 views

Restricting Insert and Update to functions

We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving ...
0
votes
0answers
19 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 = ...
3
votes
0answers
131 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 ...
2
votes
1answer
63 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
1
vote
2answers
92 views

Latitude and Longtitude Distance Calculation [closed]

I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in ...
0
votes
1answer
39 views

how to correctly return values from a function

I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not. ...
1
vote
0answers
212 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
6
votes
2answers
237 views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
1
vote
2answers
207 views

Datediff() in MySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below. select datediff((select curdate()), (select ...
1
vote
1answer
67 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
1
vote
2answers
65 views

Filter by value or show all if no value

I have 2 tables: records and access_rights Access table has list of permission for certain and should be used to filter records based on rules: function field is mandatory object field is ...
0
votes
1answer
28 views

What is the MS_PerfDashboard schema in msdb?

We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of ...
0
votes
1answer
177 views

Use string functions and arithmetic calculations in SQL query or by programming language?

I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)? $result = ...
1
vote
3answers
99 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
5
votes
2answers
229 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
0
votes
1answer
52 views

How to add up a column depending on data from another column

I have a table which has results and a table that has band_name and band_id. Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I ...
1
vote
0answers
106 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 ...
3
votes
2answers
347 views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
0
votes
1answer
257 views

Calling another function inside a postgres function

I have a function A and function B. Function A Name is f_a() and the code is: Select id,address from A; Function B Name is f_b() code is: Select C.address,B.name From (Select id,name from B, ...
1
vote
1answer
2k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
1
vote
3answers
245 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
3
votes
1answer
692 views

Table-valued function runs fast, but slow from another function [closed]

Disclaimer: I'm a senior sysadmin who's trying to help our development team speed up the production database. Our users are hurting, bad. Please don't shoot me for not being great with SQL The ...
3
votes
1answer
98 views

PL/pgSQL functions and optimizations fences?

I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and ...
0
votes
1answer
170 views

use function arguments or variable in postgres function

I want to set the default value for the column building and floor in my table points in the below function called test(b,f) Create or replace function test(b text, f text) returns void as $$ Begin ...
1
vote
1answer
302 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
2answers
430 views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
0
votes
1answer
94 views

Selecting from a function that returns a sys refcursor or an alternative

I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can: Create a view and then query it. Create a function and return resultset If I ...
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 ...
1
vote
1answer
2k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
1
vote
0answers
111 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
2answers
578 views

Reducing size of image returned by view

We are storing a list of images in a table in an image column. These are stored as their raw image size so they can be viewed in their details at full resolution. The problem I have is I want to show ...
1
vote
1answer
563 views

How to call an Oracle Function with multi out Parameters though LINKSERVER

I need help as I am facing this problem. when I pass 2 output parameter to a function through a linked server I am getting this error. For 1 out parameter its working fine but when I use two output ...
3
votes
1answer
300 views

Running functions in parallel

This is for SQL Server 2012. We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
2
votes
1answer
862 views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
-1
votes
1answer
72 views

checking a folder whether required file is present at a regular interval [closed]

i have 4 folder hierarchy in a following format : Hourly Daily Weekly Monthly. In every folder i have files which is coming in a frequency based on their folder's name. Now , i have ...
1
vote
2answers
1k views

Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?

I have a function that returns a table. This function SELECTS DB1.dbo.table1 user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the ...
1
vote
1answer
858 views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
0answers
277 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 ...