We could do without it, but it's simpler: CREATE TYPE foobar AS (description text, type text) įunction: CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar) Then use this to replace the VALUES expression in above statement: SELECT split_part(x, ',', 1) AS descriptionįROM unnest(string_to_array(_param, ' ')) x įunction with UPSERT in Postgres 9.5 or laterĬreate a custom row type for parameter passing. For instance of the form: 'description1,type1 description2,type2 description3,type3' Or, if the syntax for such an array seems too messy, use a comma-separated string as parameter _param. Function for repeated useĬreate an SQL function that takes an array of composite type as parameter and use unnest(param) in place of the VALUES expression.

In comparison to caching solutions like advertised in another answer, the chance is super-tiny. Really only happens under heavy concurrent load, if ever.

How do I implement insert-if-not-found for transactions at serializable isolation level?.

Is SELECT or INSERT in a function prone to race conditions?.

There is a tiny race condition if you run multiple of these queries concurrently. Referential integrity is enforced at the end of the statement by default. Strictly speaking, both inserts happen 'in parallel', but since this is a single statement, default FOREIGN KEY constraints will not complain. The final outer INSERT can now insert a foo_id for every row: either the type pre-existed, or it was inserted in step 2. The 2nd CTE ins inserts distinct new types ( foo_id IS NULL) into foo, and returns the newly generated foo_id - together with the type to join back to insert rows. All other rows get foo_id IS NULL this way. Immediately LEFT JOIN to foo to append the foo_id for pre-existing type rows. The subquery val with the VALUES expression can be replaced with a table or subquery as source. The 1st CTE sel provides multiple rows of input data. Two rows to illustrate the need for DISTINCT in the first INSERT statement. Both are purple, which does not exist in foo, yet. Old sqlfiddle for Postgres 9.6 - works the same in 9.1. SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id) SELECT DISTINCT type FROM sel WHERE foo_id IS NULL SELECT val.description, val.type, f.id AS foo_id To create missing entries in foo on the fly, in a single SQL statement, CTEs are instrumental: WITH sel AS ( It's enough to do it in the first row, the rest falls in line. ), types cannot be derived and default data types are used unless typed explicitly. Since the VALUES expression is not directly attached to a table (like in INSERT. Subqueries are typically a bit faster when none of the above is needed. Common Table Expressions offer additional features and are easier to read in big queries, but they also pose as optimization barriers (up to Postgres 12).

The VALUES expression in the subquery does the same as CTE. Instead, NULL is entered for foo_id (which raises an exception if the column is defined NOT NULL). LEFT JOIN instead of JOIN means that rows from val all rows are kept, even when no match is found in foo. , ('new row1', 'purple') - purple does not exist in foo, yet (text 'testing', text 'blue') - explicit type declaration see below Plain INSERT INSERT INTO bar (description, foo_id) Model ): # notice it's the same syntax as models.ForeignKeyĬhoice = models. Objects = PollManager () # this is how it displays in the autocompletion.should be related to # how the person filters above def ajax_str ( self ): return self. DateTimeField ( 'date published' ) # set the manager order_by ( 'question' ) class Poll (models. Manager ): def ajax_autocomplete ( self, data ): return self. # notice the manager, we can create a staticmethod in Poll if we wanted. Put the following in your urls configuration:įrom ajaxfkey.fields import AjaxForeignKey (It certainly does not need to be inside Django's source.) Put the contents below in a place locatable by 'admin/base_site.html' in your setup. Creating base_templates/admin/base_site.html If you downloaded fields.py, you need to make sure the import line at the top correctly imports the Form field.įrom import AjaxForeignKeyFormField Modify your models to use it correctly.Modify your urls conf to contain the ajax view.Create a base_site.html file in your base_templates/admin directory.fields.py - contains the actual model field.Create a new folder in your project called ajaxfkey.Using YUI, create a new field similar to models.ForeignKey that renders differently in the admin. I know raw_id_admin works for foreign keys to big tables, but AJAX is just so much cooler. In the Admin, I would like to have an autocompletion field for ForeignKey references. Autocompletion for ForeignKeys in Admin (YUI) The Problem

Getorgchart ajax foreign key